"""
Phase 3: Deep Analysis - Portfolio Composition, Capabilities, Vendor Ecosystem, 
          Ontology/Knowledge Graph, Data Quality, and Consolidation Opportunities
"""

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

# Define paths
DATA_PATH = "temp_files/cleaned_portfolio.csv"
OUTPUT_DIR = "temp_files"
IMAGES_HTML_DIR = "assets/images/html"
IMAGES_PNG_DIR = "assets/images/png"
REPORTS_DIR = "assets/reports"

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

# Load cleaned data
print("=" * 80)
print("PHASE 3: DEEP ANALYSIS")
print("=" * 80)

print("\n[1] Loading cleaned portfolio data...")
df = pd.read_csv(DATA_PATH)
print(f"    Loaded {len(df)} applications")

# Reload original for some comparisons
df_orig = pd.read_excel("user_source_data/Schedule B App Catalogue Extract March 2026.xlsx")
df_orig.columns = df_orig.columns.str.replace('\n', ' ').str.strip()

# =====================================
# PART 1: PORTFOLIO COMPOSITION ANALYSIS
# =====================================
print("\n" + "=" * 60)
print("PART 1: PORTFOLIO COMPOSITION ANALYSIS")
print("=" * 60)

# 1.1 Domain and Sub-Domain Coverage Mapping
print("\n[1.1] Domain and Sub-Domain Coverage Mapping...")

# Domain breakdown with percentage
domain_stats = df['Domain'].value_counts().reset_index()
domain_stats.columns = ['Domain', 'Count']
domain_stats['Percentage'] = (domain_stats['Count'] / len(df) * 100).round(1)

fig_domain_detail = go.Figure()

fig_domain_detail.add_trace(go.Bar(
    x=domain_stats['Domain'],
    y=domain_stats['Count'],
    marker_color=px.colors.qualitative.Bold,
    text=domain_stats['Count'],
    textposition='outside',
    name='Applications'
))

fig_domain_detail.add_trace(go.Scatter(
    x=domain_stats['Domain'],
    y=domain_stats['Percentage'],
    yaxis='y2',
    mode='lines+markers',
    name='% of Portfolio',
    line=dict(color='red', width=2)
))

fig_domain_detail.update_layout(
    title='Application Distribution by Business Domain',
    xaxis_title='Domain',
    yaxis_title='Number of Applications',
    yaxis2=dict(
        title='Percentage (%)',
        overlaying='y',
        side='right',
        range=[0, 50]
    ),
    height=500,
    width=1000,
    legend=dict(x=0.5, y=1.1, orientation='h', xanchor='center'),
    xaxis=dict(tickangle=30)
)

domain_detail_path = os.path.join(IMAGES_HTML_DIR, "domain_coverage_detailed.html")
fig_domain_detail.write_html(domain_detail_path)

# Sub-domain coverage by domain
subdomain_pivot = df.groupby(['Domain', 'Sub Domain']).size().reset_index(name='Count')

# Top 10 most populated sub-domains
top_subdomains = df['Sub Domain'].value_counts().head(10).reset_index()
top_subdomains.columns = ['Sub Domain', 'Count']

fig_top_subdomain = px.bar(
    top_subdomains,
    x='Count',
    y='Sub Domain',
    orientation='h',
    color='Count',
    color_continuous_scale='Cividis',
    title='Top 10 Most Populated Sub-Domains',
    text='Count'
)
fig_top_subdomain.update_layout(
    xaxis_title='Number of Applications',
    yaxis_title='Sub Domain',
    height=500,
    width=900,
    yaxis=dict(categoryorder='total ascending')
)

subdomain_top_path = os.path.join(IMAGES_HTML_DIR, "top_subdomains.html")
fig_top_subdomain.write_html(subdomain_top_path)

# 1.2 Application Type and Category Breakdown
print("\n[1.2] Application Type and Category Breakdown...")

# Application Type by Domain
app_type_domain = pd.crosstab(df['Domain'], df['Application Type'])
app_type_domain_pct = app_type_domain.div(app_type_domain.sum(axis=1), axis=0) * 100

fig_app_type_heatmap = go.Figure(data=go.Heatmap(
    z=app_type_domain_pct.values,
    x=app_type_domain_pct.columns,
    y=app_type_domain_pct.index,
    colorscale='Blues',
    text=np.round(app_type_domain_pct.values, 1),
    texttemplate='%{text}%',
    textfont={"size": 9},
    hovertemplate='Domain: %{y}<br>Type: %{x}<br>%: %{text}<extra></extra>'
))

fig_app_type_heatmap.update_layout(
    title='Application Type Distribution by Domain (%)',
    xaxis_title='Application Type',
    yaxis_title='Domain',
    height=500,
    width=900
)

app_type_heatmap_path = os.path.join(IMAGES_HTML_DIR, "app_type_by_domain_heatmap.html")
fig_app_type_heatmap.write_html(app_type_heatmap_path)

# SaaS vs Non-SaaS breakdown
saas_domain = df.groupby(['Domain', 'Is_SaaS']).size().unstack(fill_value=0)
saas_domain.columns = ['Non-SaaS', 'SaaS']
saas_domain = saas_domain.reset_index()

fig_saas_domain = go.Figure()
fig_saas_domain.add_trace(go.Bar(
    x=saas_domain['Domain'],
    y=saas_domain['Non-SaaS'],
    name='Non-SaaS',
    marker_color='#1f77b4'
))
fig_saas_domain.add_trace(go.Bar(
    x=saas_domain['Domain'],
    y=saas_domain['SaaS'],
    name='SaaS',
    marker_color='#2ca02c'
))

fig_saas_domain.update_layout(
    title='SaaS vs Non-SaaS Applications by Domain',
    xaxis_title='Domain',
    yaxis_title='Number of Applications',
    barmode='group',
    height=500,
    width=900,
    xaxis=dict(tickangle=30)
)

saas_domain_path = os.path.join(IMAGES_HTML_DIR, "saas_by_domain.html")
fig_saas_domain.write_html(saas_domain_path)

# 1.3 Functional Area Cardinality Analysis
print("\n[1.3] Functional Area Cardinality Analysis...")

# Functional Area frequency
func_area_full = df['Functional Area'].value_counts().reset_index()
func_area_full.columns = ['Functional Area', 'Count']

# Identify high-frequency functional areas (potential consolidation targets)
high_freq_funcs = func_area_full[func_area_full['Count'] >= 3]
low_freq_funcs = func_area_full[func_area_full['Count'] <= 1]

print(f"    - Functional Areas with 3+ apps: {len(high_freq_funcs)}")
print(f"    - Functional Areas with only 1 app: {len(low_freq_funcs)}")

fig_func_sunburst = px.treemap(
    df[df['Functional Area'].notna()],
    path=['Domain', 'Sub Domain', 'Functional Area'],
    title='Functional Area Hierarchy (Domain > Sub Domain > Functional Area)',
    color='Domain',
    color_discrete_sequence=px.colors.qualitative.Safe
)
fig_func_sunburst.update_layout(height=700, width=1000)

func_sunburst_path = os.path.join(IMAGES_HTML_DIR, "functional_area_sunburst.html")
fig_func_sunburst.write_html(func_sunburst_path)

# =====================================
# PART 2: CAPABILITY MATURITY ASSESSMENT
# =====================================
print("\n" + "=" * 60)
print("PART 2: CAPABILITY MATURITY ASSESSMENT")
print("=" * 60)

# 2.1 Business Capability Coverage Mapping
print("\n[2.1] Business Capability Coverage Mapping...")

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

fig_cap_l0 = px.bar(
    cap_l0,
    x='Capability',
    y='Count',
    color='Count',
    color_continuous_scale='Viridis',
    title='Application Coverage by Business Capability (Level 0)',
    text='Count'
)
fig_cap_l0.update_layout(
    xaxis_title='Business Capability (Level 0)',
    yaxis_title='Number of Applications',
    height=500,
    width=1000,
    xaxis=dict(tickangle=35)
)
fig_cap_l0.update_traces(textposition='outside')

cap_l0_path = os.path.join(IMAGES_HTML_DIR, "capability_l0_coverage.html")
fig_cap_l0.write_html(cap_l0_path)

# Level 1 coverage - top 20
cap_l1 = df['Business Capabilities Level 1'].value_counts().head(20).reset_index()
cap_l1.columns = ['Capability', 'Count']

fig_cap_l1 = px.bar(
    cap_l1,
    x='Count',
    y='Capability',
    orientation='h',
    color='Count',
    color_continuous_scale='Plasma',
    title='Top 20 Business Capabilities (Level 1)',
    text='Count'
)
fig_cap_l1.update_layout(
    xaxis_title='Number of Applications',
    yaxis_title='Business Capability (Level 1)',
    height=600,
    width=1000,
    yaxis=dict(categoryorder='total ascending')
)

cap_l1_path = os.path.join(IMAGES_HTML_DIR, "capability_l1_top20.html")
fig_cap_l1.write_html(cap_l1_path)

# 2.2 Capability Linkage Completeness
print("\n[2.2] Capability Linkage Completeness Scoring...")

cap_completeness = {
    'Application Type': df['Application Type'].notna().sum() / len(df) * 100,
    'Application Category': df['Application Category'].notna().sum() / len(df) * 100,
    'Business Capabilities L0': df['Business Capabilities Level 0'].notna().sum() / len(df) * 100,
    'Business Capabilities L1': df['Business Capabilities Level 1'].notna().sum() / len(df) * 100,
    'Functional Area': df['Functional Area'].notna().sum() / len(df) * 100,
    'Functional Sub Area': df['Functional Sub Area'].notna().sum() / len(df) * 100,
}

fig_cap_complete = go.Figure(go.Bar(
    x=list(cap_completeness.values()),
    y=list(cap_completeness.keys()),
    orientation='h',
    marker_color=['green' if v >= 80 else 'orange' if v >= 60 else 'red' for v in cap_completeness.values()],
    text=[f'{v:.1f}%' for v in cap_completeness.values()],
    textposition='outside'
))

fig_cap_complete.update_layout(
    title='Capability Linkage Completeness Score',
    xaxis_title='Completeness (%)',
    yaxis_title='Field',
    height=400,
    width=900,
    xaxis=dict(range=[0, 100])
)

cap_complete_path = os.path.join(IMAGES_HTML_DIR, "capability_linkage_completeness.html")
fig_cap_complete.write_html(cap_complete_path)

# 2.3 Domain-to-Capability Alignment
print("\n[2.3] Domain-to-Capability Alignment Verification...")

# Cross-tabulation of Domain vs Capability Level 0
domain_cap = pd.crosstab(df['Domain'], df['Business Capabilities Level 0'])

# Calculate alignment score (most common capability per domain)
domain_cap_alignment = {}
for domain in df['Domain'].unique():
    domain_caps = df[df['Domain'] == domain]['Business Capabilities Level 0'].value_counts()
    if len(domain_caps) > 0:
        top_cap = domain_caps.index[0]
        alignment_pct = domain_caps.iloc[0] / domain_caps.sum() * 100
        domain_cap_alignment[domain] = {'top_capability': top_cap, 'alignment_score': alignment_pct}

print("    Domain-to-Capability Alignment:")
for domain, data in domain_cap_alignment.items():
    print(f"      - {domain}: {data['top_capability']} ({data['alignment_score']:.1f}%)")

# =====================================
# PART 3: VENDOR ECOSYSTEM ANALYSIS
# =====================================
print("\n" + "=" * 60)
print("PART 3: VENDOR ECOSYSTEM ANALYSIS")
print("=" * 60)

# 3.1 Supplier Diversity and Concentration
print("\n[3.1] Supplier Diversity and Concentration Analysis...")

supplier_counts = df['Supplier'].value_counts()
total_apps = len(df)
unique_suppliers = len(supplier_counts)

# Herfindahl-Hirschman Index (HHI)
market_shares = (supplier_counts / total_apps) ** 2
hhi = market_shares.sum() * 10000

# Top 5 suppliers concentration
top5_count = supplier_counts.head(5).sum()
top5_concentration = top5_count / total_apps * 100

# Top 10 suppliers concentration
top10_count = supplier_counts.head(10).sum()
top10_concentration = top10_count / total_apps * 100

print(f"    - Unique Suppliers: {unique_suppliers}")
print(f"    - HHI Index: {hhi:.1f} (Higher = more concentrated)")
print(f"    - Top 5 Concentration: {top5_concentration:.1f}%")
print(f"    - Top 10 Concentration: {top10_concentration:.1f}%")

# Supplier concentration visualization
fig_supplier_concentration = go.Figure()

# Bar chart for top 15 suppliers
top15_suppliers = supplier_counts.head(15).reset_index()
top15_suppliers.columns = ['Supplier', 'Count']

fig_supplier_concentration.add_trace(go.Bar(
    x=top15_suppliers['Supplier'],
    y=top15_suppliers['Count'],
    marker_color=px.colors.qualitative.Set2,
    name='Applications'
))

# Add cumulative percentage line
top15_suppliers['Cumulative'] = top15_suppliers['Count'].cumsum() / total_apps * 100
fig_supplier_concentration.add_trace(go.Scatter(
    x=top15_suppliers['Supplier'],
    y=top15_suppliers['Cumulative'],
    yaxis='y2',
    mode='lines+markers',
    name='Cumulative %',
    line=dict(color='red', width=2)
))

fig_supplier_concentration.update_layout(
    title='Supplier Concentration Analysis (Top 15)',
    xaxis_title='Supplier',
    yaxis_title='Number of Applications',
    yaxis2=dict(title='Cumulative %', overlaying='y', side='right', range=[0, 100]),
    height=500,
    width=1000,
    xaxis=dict(tickangle=45),
    legend=dict(x=0.5, y=1.1, orientation='h', xanchor='center')
)

supplier_conc_path = os.path.join(IMAGES_HTML_DIR, "supplier_concentration.html")
fig_supplier_concentration.write_html(supplier_conc_path)

# 3.2 Strategic Vendor Clustering
print("\n[3.2] Strategic Vendor Clustering...")

# Define vendor tiers based on app count
vendor_tiers = pd.cut(supplier_counts, 
                      bins=[0, 1, 5, 15, float('inf')], 
                      labels=['Tier 4 (1 app)', 'Tier 3 (2-5)', 'Tier 2 (6-15)', 'Tier 1 (16+)'])

tier_counts = vendor_tiers.value_counts().sort_index()

fig_vendor_tiers = px.pie(
    values=tier_counts.values,
    names=tier_counts.index,
    title='Vendor Clustering by Application Portfolio Size',
    hole=0.4,
    color_discrete_sequence=px.colors.qualitative.Pastel
)
fig_vendor_tiers.update_layout(height=500, width=700)

vendor_tiers_path = os.path.join(IMAGES_HTML_DIR, "vendor_tiers.html")
fig_vendor_tiers.write_html(vendor_tiers_path)

# 3.3 SaaS Adoption Rate Analysis
print("\n[3.3] SaaS Adoption Rate Analysis...")

saas_by_domain = df.groupby('Domain')['Is_SaaS'].agg(['sum', 'count'])
saas_by_domain['SaaS Rate'] = (saas_by_domain['sum'] / saas_by_domain['count'] * 100).round(1)
saas_by_domain = saas_by_domain.reset_index()
saas_by_domain.columns = ['Domain', 'SaaS Apps', 'Total Apps', 'SaaS Rate']

fig_saas_rate = px.bar(
    saas_by_domain,
    x='Domain',
    y='SaaS Rate',
    color='SaaS Rate',
    color_continuous_scale='Teal',
    title='SaaS Adoption Rate by Domain (%)',
    text='SaaS Rate'
)
fig_saas_rate.update_layout(
    xaxis_title='Domain',
    yaxis_title='SaaS Adoption Rate (%)',
    height=500,
    width=900,
    xaxis=dict(tickangle=30),
    yaxis=dict(range=[0, 30])
)
fig_saas_rate.update_traces(textposition='outside')

saas_rate_path = os.path.join(IMAGES_HTML_DIR, "saas_adoption_rate.html")
fig_saas_rate.write_html(saas_rate_path)

# =====================================
# PART 4: ONTOLOGY & KNOWLEDGE GRAPH
# =====================================
print("\n" + "=" * 60)
print("PART 4: ONTOLOGY & KNOWLEDGE GRAPH")
print("=" * 60)

# 4.1 Application Relationship Graph (Sankey Diagram)
print("\n[4.1] Building Application Relationship Graph...")

# Create source-target pairs for relationships
# Domain -> Sub Domain relationships
domain_subdomain = df.groupby(['Domain', 'Sub Domain']).size().reset_index(name='Count')
domain_subdomain = domain_subdomain[domain_subdomain['Count'] >= 2]  # Filter for meaningful relationships

# Create nodes list
all_nodes = list(set(domain_subdomain['Domain'].unique()) | set(domain_subdomain['Sub Domain'].unique()))
node_map = {node: i for i, node in enumerate(all_nodes)}

# Create Sankey data
sources = [node_map[d] for d in domain_subdomain['Domain']]
targets = [node_map[s] for s in domain_subdomain['Sub Domain']]
values = domain_subdomain['Count'].tolist()

# Color palette
node_colors = ['#1f77b4', '#ff7f0e', '#2ca02c', '#d62728', '#9467bd', '#8c564b', '#e377c2', '#7f7f7f', '#bcbd22']

fig_sankey = go.Figure(data=[go.Sankey(
    node=dict(
        pad=15,
        thickness=20,
        line=dict(color='black', width=0.5),
        label=all_nodes,
        color=node_colors[:len(all_nodes)]
    ),
    link=dict(
        source=sources,
        target=targets,
        value=values,
        color='rgba(150,150,150,0.4)'
    )
)])

fig_sankey.update_layout(
    title='Application Relationship Graph: Domain → Sub-Domain Flow',
    height=700,
    width=1000
)

sankey_path = os.path.join(IMAGES_HTML_DIR, "domain_subdomain_sankey.html")
fig_sankey.write_html(sankey_path)

# 4.2 Enterprise Capability Ontology Model
print("\n[4.2] Constructing Enterprise Capability Ontology...")

# Create hierarchy mapping - only use rows where both L0 and L1 are present
cap_hierarchy = df[['Business Capabilities Level 0', 'Business Capabilities Level 1']].dropna()
cap_hierarchy = cap_hierarchy.drop_duplicates()
cap_hierarchy = cap_hierarchy.sort_values('Business Capabilities Level 0')

# Create sunburst for capability ontology - filter to only complete hierarchies
cap_sunburst = df[(df['Business Capabilities Level 0'].notna()) & 
                  (df['Business Capabilities Level 1'].notna())].copy()

if len(cap_sunburst) > 0:
    fig_cap_ontology = px.sunburst(
        cap_sunburst,
        path=['Business Capabilities Level 0', 'Business Capabilities Level 1'],
        title='Enterprise Capability Ontology (Level 0 → Level 1)',
        color='Business Capabilities Level 0',
        color_discrete_sequence=px.colors.qualitative.Bold
    )
    fig_cap_ontology.update_layout(height=700, width=700)
else:
    # Fallback to just Level 0 if no complete hierarchies
    fig_cap_ontology = px.sunburst(
        df[df['Business Capabilities Level 0'].notna()],
        path=['Business Capabilities Level 0'],
        title='Enterprise Capability Ontology (Level 0 Only)',
        color='Business Capabilities Level 0',
        color_discrete_sequence=px.colors.qualitative.Bold
    )
    fig_cap_ontology.update_layout(height=700, width=700)

cap_ontology_path = os.path.join(IMAGES_HTML_DIR, "capability_ontology.html")
fig_cap_ontology.write_html(cap_ontology_path)

# 4.3 Vendor-Application Dependency Network
print("\n[4.3] Building Vendor-Application Dependency Network...")

# Top vendors and their applications
top_vendors = ['Microsoft', 'SAP', 'Kyndryl', 'IBM', 'Accenture', 'Salesforce', 'Blue Yonder', 'Oracle', 'Bespoke']
vendor_apps = df[df['Supplier'].isin(top_vendors)][['Supplier', 'Title', 'Domain', 'Application Type']].copy()

# Group by vendor and get domain distribution
vendor_domain = pd.crosstab(vendor_apps['Supplier'], vendor_apps['Domain'])

fig_vendor_network = go.Figure()

for i, vendor in enumerate(vendor_domain.index):
    fig_vendor_network.add_trace(go.Bar(
        name=vendor,
        x=vendor_domain.columns,
        y=vendor_domain.loc[vendor],
        visible=True if i == 0 else False,  # Show first vendor only, others in dropdown
        marker=dict(color=px.colors.qualitative.Set1[i])
    ))

fig_vendor_network.update_layout(
    title='Vendor Application Portfolio by Domain',
    xaxis_title='Domain',
    yaxis_title='Number of Applications',
    height=500,
    width=1000,
    updatemenus=[dict(
        active=0,
        buttons=[dict(
            label=vendor,
            method='update',
            args=[{'visible': [j == i for j in range(len(vendor_domain.index))]},
                  {'title': f'Application Portfolio: {vendor}'}]
        ) for i, vendor in enumerate(vendor_domain.index)]
    )]
)

vendor_network_path = os.path.join(IMAGES_HTML_DIR, "vendor_dependency_network.html")
fig_vendor_network.write_html(vendor_network_path)

# =====================================
# PART 5: DATA QUALITY ANALYSIS
# =====================================
print("\n" + "=" * 60)
print("PART 5: DATA QUALITY ANALYSIS")
print("=" * 60)

# 5.1 Null Pattern Heatmap
print("\n[5.1] Generating Null Pattern Heatmap...")

# Create null pattern matrix
null_matrix = df.isnull().astype(int)
null_by_field = null_matrix.sum() / len(null_matrix) * 100

# Group by Domain and show null patterns
domain_null = df.groupby('Domain').apply(lambda x: (x.isnull().sum() / len(x) * 100)).reset_index()

fig_null_heatmap = go.Figure(data=go.Heatmap(
    z=domain_null.iloc[:, 1:].values,
    x=domain_null.columns[1:],
    y=domain_null['Domain'],
    colorscale='RdYlGn_r',
    text=np.round(domain_null.iloc[:, 1:].values, 1),
    texttemplate='%{text}%',
    textfont={"size": 8},
    hovertemplate='Domain: %{y}<br>Field: %{x}<br>Missing: %{text}<extra></extra>'
))

fig_null_heatmap.update_layout(
    title='Data Quality Heatmap: Missing Values by Domain (%)',
    xaxis_title='Field',
    yaxis_title='Domain',
    height=500,
    width=1000
)

null_heatmap_path = os.path.join(IMAGES_HTML_DIR, "null_pattern_heatmap.html")
fig_null_heatmap.write_html(null_heatmap_path)

# 5.2 Duplicate Detection
print("\n[5.2] Duplicate Detection and Identifier Reconciliation...")

# Check original data for duplicates
original_duplicates = df_orig[df_orig.duplicated(subset=['Title'], keep=False)]
print(f"    - Potential duplicate Titles: {len(original_duplicates)} rows")

# Alternative Application Name variants
alt_name_dupes = df_orig[df_orig.duplicated(subset=['Alternative Application Name'], keep=False)]
print(f"    - Alternative Name duplicates: {len(alt_name_dupes)} rows")

# Title vs Alternative Name matching (potential semantic duplicates)
df_orig['Title_clean'] = df_orig['Title'].str.lower().str.strip()
df_orig['Alt_clean'] = df_orig['Alternative Application Name'].str.lower().str.strip()

# Find applications where Title is different from Alternative Name but similar
from difflib import SequenceMatcher

def similar(a, b):
    return SequenceMatcher(None, a, b).ratio()

similar_pairs = []
for idx, row in df_orig.iterrows():
    if pd.notna(row['Title']) and pd.notna(row['Alternative Application Name']):
        similarity = similar(str(row['Title']), str(row['Alternative Application Name']))
        if 0.6 <= similarity < 1.0:
            similar_pairs.append({
                'Title': row['Title'],
                'Alt Name': row['Alternative Application Name'],
                'Similarity': similarity
            })

similar_df = pd.DataFrame(similar_pairs)
print(f"    - Semantically similar Title/Alt Name pairs: {len(similar_df)}")

# 5.3 Data Enrichment Framework
print("\n[5.3] Developing Enrichment Framework...")

enrichment_scores = {
    'Application Type': (1 - df['Application Type'].isnull().sum() / len(df)) * 100,
    'Application Category (SaaS)': (1 - df['Application Category'].isnull().sum() / len(df)) * 100,
    'Business Capabilities Level 0': (1 - df['Business Capabilities Level 0'].isnull().sum() / len(df)) * 100,
    'Business Capabilities Level 1': (1 - df['Business Capabilities Level 1'].isnull().sum() / len(df)) * 100,
    'Functional Sub Area': (1 - df['Functional Sub Area'].isnull().sum() / len(df)) * 100,
    'Supplier': (1 - (df['Supplier'] == 'Unknown').sum() / len(df)) * 100,
}

fig_enrichment = px.bar(
    x=list(enrichment_scores.keys()),
    y=list(enrichment_scores.values()),
    color=list(enrichment_scores.values()),
    color_continuous_scale='RdYlGn',
    title='Data Enrichment Scores by Field',
    text=[f'{v:.1f}%' for v in enrichment_scores.values()]
)
fig_enrichment.update_layout(
    xaxis_title='Field',
    yaxis_title='Enrichment Score (%)',
    height=400,
    width=1000,
    xaxis=dict(tickangle=30),
    yaxis=dict(range=[0, 100])
)
fig_enrichment.update_traces(textposition='outside')

enrichment_path = os.path.join(IMAGES_HTML_DIR, "enrichment_scores.html")
fig_enrichment.write_html(enrichment_path)

# =====================================
# PART 6: CONSOLIDATION OPPORTUNITIES
# =====================================
print("\n" + "=" * 60)
print("PART 6: CONSOLIDATION OPPORTUNITIES")
print("=" * 60)

# 6.1 Identify Functional Areas with Multiple Apps
print("\n[6.1] Identifying Functional Areas with Consolidation Potential...")

# Group by Functional Area and count applications
func_consolidation = df[df['Functional Area'].notna()].groupby('Functional Area').agg({
    'Title': 'count',
    'Supplier': lambda x: ', '.join(x.unique()),
    'Domain': lambda x: x.mode()[0] if len(x.mode()) > 0 else 'N/A'
}).reset_index()
func_consolidation.columns = ['Functional Area', 'App Count', 'Suppliers', 'Domain']
func_consolidation = func_consolidation.sort_values('App Count', ascending=False)

# Filter for areas with 3+ apps (high consolidation potential)
high_duplicate_funcs = func_consolidation[func_consolidation['App Count'] >= 3]

print(f"    - Functional areas with 3+ apps: {len(high_duplicate_funcs)}")
print("\n    Top consolidation candidates:")
for _, row in high_duplicate_funcs.head(10).iterrows():
    print(f"      - {row['Functional Area']}: {row['App Count']} apps ({row['Suppliers'][:50]}...)")

# Visualization
fig_consolidation = go.Figure(go.Treemap(
    labels=func_consolidation['Functional Area'].head(30),
    parents=func_consolidation['Domain'].head(30),
    values=func_consolidation['App Count'].head(30),
    textinfo='label+value',
    marker=dict(
        colors=func_consolidation['App Count'].head(30),
        colorscale='Reds'
    )
))

fig_consolidation.update_layout(
    title='Consolidation Opportunities: Functional Areas with Multiple Applications',
    height=700,
    width=1000
)

consolidation_path = os.path.join(IMAGES_HTML_DIR, "consolidation_opportunities.html")
fig_consolidation.write_html(consolidation_path)

# 6.2 Bespoke Applications (Consolidation Targets)
print("\n[6.2] Bespoke Applications Analysis (Modernization Targets)...")

bespoke_apps = df[df['Supplier'] == 'Bespoke'][['Title', 'Domain', 'Sub Domain', 'Functional Area', 'Application Type']]
bespoke_by_domain = bespoke_apps['Domain'].value_counts()

fig_bespoke = px.bar(
    bespoke_by_domain.reset_index(),
    x='Domain',
    y='count',
    color='count',
    color_continuous_scale='Oranges',
    title='Bespoke Applications by Domain (Consolidation Targets)',
    text='count'
)
fig_bespoke.update_layout(
    xaxis_title='Domain',
    yaxis_title='Number of Bespoke Apps',
    height=500,
    width=900,
    xaxis=dict(tickangle=30)
)
fig_bespoke.update_traces(textposition='outside')

bespoke_path = os.path.join(IMAGES_HTML_DIR, "bespoke_apps_by_domain.html")
fig_bespoke.write_html(bespoke_path)

# 6.3 Vendor Consolidation Recommendations
print("\n[6.3] Vendor Consolidation Recommendations...")

# Find applications that could be consolidated to strategic platforms
strategic_vendors = ['Microsoft', 'SAP', 'Salesforce', 'Blue Yonder', 'Oracle', 'Adobe', 'Google']

# Current strategic vendor apps
strategic_apps = df[df['Supplier'].isin(strategic_vendors)]['Domain'].value_counts().reset_index()
strategic_apps.columns = ['Domain', 'Strategic Apps']

# Non-strategic (bespoke + others)
non_strategic = df[~df['Supplier'].isin(strategic_vendors + ['Unknown'])]['Domain'].value_counts().reset_index()
non_strategic.columns = ['Domain', 'Non-Strategic Apps']

# Merge
vendor_consolidation = pd.merge(strategic_apps, non_strategic, on='Domain', how='outer').fillna(0)
vendor_consolidation['Total'] = vendor_consolidation['Strategic Apps'] + vendor_consolidation['Non-Strategic Apps']
vendor_consolidation['Strategic Ratio'] = (vendor_consolidation['Strategic Apps'] / vendor_consolidation['Total'] * 100).round(1)

fig_vendor_recommend = go.Figure()

fig_vendor_recommend.add_trace(go.Bar(
    x=vendor_consolidation['Domain'],
    y=vendor_consolidation['Strategic Apps'],
    name='Strategic Vendors',
    marker_color='#2ca02c'
))

fig_vendor_recommend.add_trace(go.Bar(
    x=vendor_consolidation['Domain'],
    y=vendor_consolidation['Non-Strategic Apps'],
    name='Non-Strategic (Consolidation Target)',
    marker_color='#d62728'
))

fig_vendor_recommend.update_layout(
    title='Vendor Consolidation Status by Domain',
    xaxis_title='Domain',
    yaxis_title='Number of Applications',
    barmode='stack',
    height=500,
    width=1000,
    xaxis=dict(tickangle=30)
)

vendor_recommend_path = os.path.join(IMAGES_HTML_DIR, "vendor_consolidation_recommendations.html")
fig_vendor_recommend.write_html(vendor_recommend_path)

# =====================================
# SAVE SUMMARY REPORT
# =====================================
print("\n" + "=" * 60)
print("SAVING ANALYSIS SUMMARY")
print("=" * 60)

summary_text = """
================================================================================
PHASE 3: DEEP ANALYSIS SUMMARY
================================================================================

PORTFOLIO COMPOSITION:
- Total Applications: {total}
- Unique Domains: {domains}
- Unique Sub-Domains: {subdomains}
- Unique Functional Areas: {func_areas}
- Unique Suppliers: {suppliers}

KEY FINDINGS:

1. DOMAIN DISTRIBUTION
   - Enabling Technology leads with {et_pct}% of applications
   - Enterprise follows with {ent_pct}%
   - 9 business domains identified

2. APPLICATION TYPES
   - Business Applications: {ba_count} ({ba_pct}%)
   - Infrastructure: {inf_count} ({inf_pct}%)
   - SaaS Applications: {saas_count} ({saas_pct}%)

3. VENDOR ECOSYSTEM
   - Unique Suppliers: {suppliers}
   - Top 5 Concentration: {top5_conc}%
   - HHI Index: {hhi:.1f}
   - Bespoke Apps: {bespoke_count} ({bespoke_pct}%)

4. DATA QUALITY
   - Application Category completeness: {app_cat_comp}%
   - Business Capabilities L0 completeness: {cap_l0_comp}%
   - Business Capabilities L1 completeness: {cap_l1_comp}%
   - Functional Sub Area completeness: {func_sub_comp}%

5. CONSOLIDATION OPPORTUNITIES
   - Functional areas with 3+ apps: {high_dup_funcs}
   - Bespoke applications: {bespoke_count}
   - Domains for modernization focus identified

FILES GENERATED:
- Multiple HTML visualizations in assets/images/html/
- Cleaned dataset saved to temp_files/cleaned_portfolio.csv
""".format(
    total=len(df),
    domains=df['Domain'].nunique(),
    subdomains=df['Sub Domain'].nunique(),
    func_areas=df['Functional Area'].nunique(),
    suppliers=unique_suppliers,
    et_pct=domain_stats[domain_stats['Domain'] == 'Enabling Technology']['Percentage'].values[0],
    ent_pct=domain_stats[domain_stats['Domain'] == 'Enterprise']['Percentage'].values[0],
    ba_count=len(df[df['Application Type'] == 'Business Application']),
    ba_pct=round(len(df[df['Application Type'] == 'Business Application']) / len(df) * 100, 1),
    inf_count=len(df[df['Application Type'] == 'Infrastructure']),
    inf_pct=round(len(df[df['Application Type'] == 'Infrastructure']) / len(df) * 100, 1),
    saas_count=df['Is_SaaS'].sum(),
    saas_pct=round(df['Is_SaaS'].sum() / len(df) * 100, 1),
    top5_conc=round(top5_concentration, 1),
    hhi=round(hhi, 1),
    bespoke_count=len(df[df['Supplier'] == 'Bespoke']),
    bespoke_pct=round(len(df[df['Supplier'] == 'Bespoke']) / len(df) * 100, 1),
    app_cat_comp=round((1 - df['Application Category'].isnull().sum() / len(df)) * 100, 1),
    cap_l0_comp=round((1 - df['Business Capabilities Level 0'].isnull().sum() / len(df)) * 100, 1),
    cap_l1_comp=round((1 - df['Business Capabilities Level 1'].isnull().sum() / len(df)) * 100, 1),
    func_sub_comp=round((1 - df['Functional Sub Area'].isnull().sum() / len(df)) * 100, 1),
    high_dup_funcs=len(high_duplicate_funcs)
)

summary_path = os.path.join(OUTPUT_DIR, "deep_analysis_summary.txt")
with open(summary_path, 'w') as f:
    f.write(summary_text)

print(f"\n- Summary saved to: {summary_path}")
print("\n" + "=" * 80)
print("Phase 3 Complete - Deep Analysis Finished")
print("=" * 80)