from customer_retention.analysis.notebook_progress import track_and_export_previous

track_and_export_previous("04_column_deep_dive.ipynb")

import numpy as np
import pandas as pd
import plotly.graph_objects as go
from scipy import stats

from customer_retention.analysis.auto_explorer import ExplorationFindings, RecommendationRegistry
from customer_retention.analysis.visualization import ChartBuilder, console, display_figure, display_table
from customer_retention.core.config.column_config import ColumnType
from customer_retention.core.config.experiments import (
    FINDINGS_DIR,
)
from customer_retention.stages.profiling import (
    CategoricalDistributionAnalyzer,
    DistributionAnalyzer,
    TemporalAnalyzer,
    TemporalGranularity,
    TransformationType,
)
from customer_retention.stages.validation import DataValidator, RuleGenerator

from customer_retention.analysis.auto_explorer import load_notebook_findings

FINDINGS_PATH, _namespace, dataset_name = load_notebook_findings("04_column_deep_dive.ipynb")
print(f"Using: {FINDINGS_PATH}")

findings = ExplorationFindings.load(FINDINGS_PATH)
print(f"\nLoaded findings for {findings.column_count} columns from {findings.source_path}")

# Warn if this is event-level data (should run 01d first)
if findings.is_time_series and "_aggregated" not in FINDINGS_PATH:
    ts_meta = findings.time_series_metadata
    print("\n\u26a0\ufe0f  WARNING: This appears to be EVENT-LEVEL data")
    print(f"   Entity: {ts_meta.entity_column}, Time: {ts_meta.time_column}")
    print("   Recommendation: Run 01d_event_aggregation.ipynb first to create entity-level data")

Using: /Users/Vital/python/CustomerRetention/experiments/runs/email-6301db6c/datasets/customer_emails/findings/customer_emails_aggregated_findings.yaml

Loaded findings for 217 columns from /Users/Vital/python/CustomerRetention/experiments/runs/email-6301db6c/data/bronze/customer_emails_aggregated

# Load data - handle aggregated data (parquet or Delta Lake)
from pathlib import Path

from customer_retention.analysis.auto_explorer.active_dataset_store import load_active_dataset
from customer_retention.stages.temporal import TEMPORAL_METADATA_COLS

# For aggregated data, load directly from the source path
if "_aggregated" in FINDINGS_PATH:
    source_path = Path(findings.source_path)
    if not source_path.is_absolute():
        source_path = Path("..") / source_path
    if source_path.is_dir():
        from customer_retention.integrations.adapters.factory import get_delta
        df = get_delta(force_local=True).read(str(source_path))
    elif source_path.is_file():
        df = pd.read_parquet(source_path)
    else:
        df = load_active_dataset(_namespace, dataset_name)
    data_source = f"aggregated:{source_path.name}"
else:
    # Standard loading for event-level or entity-level data
    df = load_active_dataset(_namespace, dataset_name)
    data_source = dataset_name

print(f"Loaded data from: {data_source}")
print(f"Shape: {df.shape}")

charts = ChartBuilder()

# Initialize recommendation registry for this exploration
registry = RecommendationRegistry()
registry.init_bronze(findings.source_path)

# Find target column for Gold layer initialization
target_col = next((name for name, col in findings.columns.items() if col.inferred_type == ColumnType.TARGET), None)
if target_col:
    registry.init_gold(target_col)

# Find entity column for Silver layer initialization
entity_col = next((name for name, col in findings.columns.items() if col.inferred_type == ColumnType.IDENTIFIER), None)
if entity_col:
    registry.init_silver(entity_col)

print(f"Initialized recommendation registry (Bronze: {findings.source_path})")

Loaded data from: aggregated:customer_emails_aggregated
Shape: (4998, 217)
Initialized recommendation registry (Bronze: /Users/Vital/python/CustomerRetention/experiments/runs/email-6301db6c/data/bronze/customer_emails_aggregated)

validator = DataValidator()
range_rules = RuleGenerator.from_findings(findings)

console.start_section()
console.header("Value Range Validation")

if range_rules:
    range_results = validator.validate_value_ranges(df, range_rules)

    issues_found = []
    for r in range_results:
        detail = f"{r.invalid_values} invalid" if r.invalid_values > 0 else None
        console.check(f"{r.column_name} ({r.rule_type})", r.invalid_values == 0, detail)
        if r.invalid_values > 0:
            issues_found.append(r)

    all_invalid = sum(r.invalid_values for r in range_results)
    if all_invalid == 0:
        console.success("All value ranges valid")
    else:
        console.error(f"Found {all_invalid:,} values outside expected ranges")

        console.info("Examples of invalid values:")
        for r in issues_found[:3]:
            col = r.column_name
            if col in df.columns:
                if r.rule_type == 'binary':
                    invalid_mask = ~df[col].isin([0, 1, np.nan])
                    condition = "value not in [0, 1]"
                elif r.rule_type == 'non_negative':
                    invalid_mask = df[col] < 0
                    condition = "value < 0"
                elif r.rule_type == 'percentage':
                    invalid_mask = (df[col] < 0) | (df[col] > 100)
                    condition = "value < 0 or value > 100"
                elif r.rule_type == 'rate':
                    invalid_mask = (df[col] < 0) | (df[col] > 1)
                    condition = "value < 0 or value > 1"
                else:
                    continue

                invalid_values = df.loc[invalid_mask, col].dropna()
                if len(invalid_values) > 0:
                    examples = invalid_values.head(5).tolist()
                    console.metric(f"  {col}", f"{examples}")

                    # Add filtering recommendation
                    registry.add_bronze_filtering(
                        column=col, condition=condition, action="cap",
                        rationale=f"{r.invalid_values} values violate {r.rule_type} constraint",
                        source_notebook="04_column_deep_dive"
                    )

    console.info("Rules auto-generated from detected column types")
else:
    range_results = []
    console.info("No validation rules generated - no binary/numeric columns detected")

console.end_section()

# Use framework's DistributionAnalyzer for comprehensive analysis
analyzer = DistributionAnalyzer()

numeric_cols = [
    name for name, col in findings.columns.items()
    if col.inferred_type in [ColumnType.NUMERIC_CONTINUOUS, ColumnType.NUMERIC_DISCRETE]
    and name not in TEMPORAL_METADATA_COLS
]

# Analyze all numeric columns using the framework
analyses = analyzer.analyze_dataframe(df, numeric_cols)
recommendations = {col: analyzer.recommend_transformation(analysis)
                   for col, analysis in analyses.items()}

for col_name in numeric_cols:
    col_info = findings.columns[col_name]
    analysis = analyses.get(col_name)
    rec = recommendations.get(col_name)

    print(f"\n{'='*70}")
    print(f"Column: {col_name}")
    print(f"Type: {col_info.inferred_type.value} (Confidence: {col_info.confidence:.0%})")
    print("-" * 70)

    if analysis:
        print("📊 Distribution Statistics:")
        print(f"   Mean: {analysis.mean:.3f}  |  Median: {analysis.median:.3f}  |  Std: {analysis.std:.3f}")
        print(f"   Range: [{analysis.min_value:.3f}, {analysis.max_value:.3f}]")
        print(f"   Percentiles: 1%={analysis.percentiles['p1']:.3f}, 25%={analysis.q1:.3f}, 75%={analysis.q3:.3f}, 99%={analysis.percentiles['p99']:.3f}")
        print("\n📈 Shape Analysis:")
        skew_label = '(Right-skewed)' if analysis.skewness > 0.5 else '(Left-skewed)' if analysis.skewness < -0.5 else '(Symmetric)'
        print(f"   Skewness: {analysis.skewness:.2f} {skew_label}")
        kurt_label = '(Heavy tails/outliers)' if analysis.kurtosis > 3 else '(Light tails)'
        print(f"   Kurtosis: {analysis.kurtosis:.2f} {kurt_label}")
        print(f"   Zeros: {analysis.zero_count:,} ({analysis.zero_percentage:.1f}%)")
        print(f"   Outliers (IQR): {analysis.outlier_count_iqr:,} ({analysis.outlier_percentage:.1f}%)")

        if rec:
            print(f"\n🔧 Recommended Transformation: {rec.recommended_transform.value}")
            print(f"   Reason: {rec.reason}")
            print(f"   Priority: {rec.priority}")
            if rec.warnings:
                for warn in rec.warnings:
                    print(f"   ⚠️ {warn}")

    # Create enhanced histogram with Plotly
    data = df[col_name].dropna()
    fig = go.Figure()

    fig.add_trace(go.Histogram(x=data, nbinsx=50, name='Distribution',
                                marker_color='steelblue', opacity=0.7))

    # Calculate mean and median
    mean_val = data.mean()
    median_val = data.median()

    # Position labels on opposite sides (left/right) to avoid overlap
    # The larger value gets right-justified, smaller gets left-justified
    mean_position = "top right" if mean_val >= median_val else "top left"
    median_position = "top left" if mean_val >= median_val else "top right"

    # Add mean line
    fig.add_vline(
        x=mean_val,
        line_dash="dash",
        line_color="red",
        annotation_text=f"Mean: {mean_val:.2f}",
        annotation_position=mean_position,
        annotation_font_color="red",
        annotation_bgcolor="rgba(255,255,255,0.8)"
    )

    # Add median line
    fig.add_vline(
        x=median_val,
        line_dash="solid",
        line_color="green",
        annotation_text=f"Median: {median_val:.2f}",
        annotation_position=median_position,
        annotation_font_color="green",
        annotation_bgcolor="rgba(255,255,255,0.8)"
    )

    # Add 99th percentile marker if there are outliers
    if analysis and analysis.outlier_percentage > 5:
        fig.add_vline(x=analysis.percentiles['p99'], line_dash="dot", line_color="orange",
                      annotation_text=f"99th: {analysis.percentiles['p99']:.2f}",
                      annotation_position="top right",
                      annotation_font_color="orange",
                      annotation_bgcolor="rgba(255,255,255,0.8)")

    transform_label = rec.recommended_transform.value if rec else "none"
    fig.update_layout(
        title=f"Distribution: {col_name}<br><sub>Skew: {analysis.skewness:.2f} | Kurt: {analysis.kurtosis:.2f} | Strategy: {transform_label}</sub>",
        xaxis_title=col_name,
        yaxis_title="Count",
        template='plotly_white',
        height=400
    )
    display_figure(fig)

======================================================================
Column: event_count_180d
Type: numeric_discrete (Confidence: 70%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 0.639  |  Median: 0.000  |  Std: 1.009
   Range: [0.000, 11.000]
   Percentiles: 1%=0.000, 25%=0.000, 75%=1.000, 99%=4.000

📈 Shape Analysis:
   Skewness: 2.15 (Right-skewed)
   Kurtosis: 7.46 (Heavy tails/outliers)
   Zeros: 3,084 (61.7%)
   Outliers (IQR): 285 (5.7%)

🔧 Recommended Transformation: zero_inflation_handling
   Reason: Zero-inflation (61.7%) combined with high skewness (2.15)
   Priority: high
   ⚠️ Consider creating a binary indicator for zeros plus log transform of non-zero values

======================================================================
Column: event_count_365d
Type: numeric_discrete (Confidence: 70%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 1.316  |  Median: 1.000  |  Std: 1.656
   Range: [0.000, 15.000]
   Percentiles: 1%=0.000, 25%=0.000, 75%=2.000, 99%=7.000

📈 Shape Analysis:
   Skewness: 1.61 (Right-skewed)
   Kurtosis: 4.25 (Heavy tails/outliers)
   Zeros: 2,362 (47.3%)
   Outliers (IQR): 100 (2.0%)

🔧 Recommended Transformation: zero_inflation_handling
   Reason: Significant zero-inflation (47.3%)
   Priority: medium
   ⚠️ Many zero values may indicate a mixture distribution

======================================================================
Column: event_count_all_time
Type: numeric_continuous (Confidence: 90%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 16.566  |  Median: 16.000  |  Std: 9.139
   Range: [1.000, 112.000]
   Percentiles: 1%=2.000, 25%=12.000, 75%=19.000, 99%=53.000

📈 Shape Analysis:
   Skewness: 2.61 (Right-skewed)
   Kurtosis: 16.11 (Heavy tails/outliers)
   Zeros: 0 (0.0%)
   Outliers (IQR): 298 (6.0%)

🔧 Recommended Transformation: cap_then_log
   Reason: High skewness (2.61) with significant outliers (6.0%)
   Priority: high

======================================================================
Column: opened_sum_180d
Type: numeric_discrete (Confidence: 70%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 0.152  |  Median: 0.000  |  Std: 0.424
   Range: [0.000, 4.000]
   Percentiles: 1%=0.000, 25%=0.000, 75%=0.000, 99%=2.000

📈 Shape Analysis:
   Skewness: 3.14 (Right-skewed)
   Kurtosis: 11.52 (Heavy tails/outliers)
   Zeros: 4,350 (87.0%)
   Outliers (IQR): 648 (13.0%)

🔧 Recommended Transformation: zero_inflation_handling
   Reason: Zero-inflation (87.0%) combined with high skewness (3.14)
   Priority: high
   ⚠️ Consider creating a binary indicator for zeros plus log transform of non-zero values

======================================================================
Column: opened_mean_180d
Type: numeric_discrete (Confidence: 70%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 0.232  |  Median: 0.000  |  Std: 0.366
   Range: [0.000, 1.000]
   Percentiles: 1%=0.000, 25%=0.000, 75%=0.500, 99%=1.000

📈 Shape Analysis:
   Skewness: 1.27 (Right-skewed)
   Kurtosis: 0.05 (Light tails)
   Zeros: 1,266 (66.1%)
   Outliers (IQR): 0 (0.0%)

🔧 Recommended Transformation: zero_inflation_handling
   Reason: Significant zero-inflation (66.1%)
   Priority: medium
   ⚠️ Many zero values may indicate a mixture distribution

======================================================================
Column: opened_count_180d
Type: numeric_discrete (Confidence: 70%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 0.639  |  Median: 0.000  |  Std: 1.009
   Range: [0.000, 11.000]
   Percentiles: 1%=0.000, 25%=0.000, 75%=1.000, 99%=4.000

📈 Shape Analysis:
   Skewness: 2.15 (Right-skewed)
   Kurtosis: 7.46 (Heavy tails/outliers)
   Zeros: 3,084 (61.7%)
   Outliers (IQR): 285 (5.7%)

🔧 Recommended Transformation: zero_inflation_handling
   Reason: Zero-inflation (61.7%) combined with high skewness (2.15)
   Priority: high
   ⚠️ Consider creating a binary indicator for zeros plus log transform of non-zero values

======================================================================
Column: clicked_sum_180d
Type: numeric_discrete (Confidence: 70%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 0.048  |  Median: 0.000  |  Std: 0.225
   Range: [0.000, 2.000]
   Percentiles: 1%=0.000, 25%=0.000, 75%=0.000, 99%=1.000

📈 Shape Analysis:
   Skewness: 4.78 (Right-skewed)
   Kurtosis: 23.72 (Heavy tails/outliers)
   Zeros: 4,767 (95.4%)
   Outliers (IQR): 231 (4.6%)

🔧 Recommended Transformation: zero_inflation_handling
   Reason: Zero-inflation (95.4%) combined with high skewness (4.78)
   Priority: high
   ⚠️ Consider creating a binary indicator for zeros plus log transform of non-zero values

======================================================================
Column: clicked_mean_180d
Type: numeric_discrete (Confidence: 70%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 0.072  |  Median: 0.000  |  Std: 0.219
   Range: [0.000, 1.000]
   Percentiles: 1%=0.000, 25%=0.000, 75%=0.000, 99%=1.000

📈 Shape Analysis:
   Skewness: 3.26 (Right-skewed)
   Kurtosis: 10.01 (Heavy tails/outliers)
   Zeros: 1,683 (87.9%)
   Outliers (IQR): 231 (12.1%)

🔧 Recommended Transformation: zero_inflation_handling
   Reason: Zero-inflation (87.9%) combined with high skewness (3.26)
   Priority: high
   ⚠️ Consider creating a binary indicator for zeros plus log transform of non-zero values

======================================================================
Column: clicked_count_180d
Type: numeric_discrete (Confidence: 70%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 0.639  |  Median: 0.000  |  Std: 1.009
   Range: [0.000, 11.000]
   Percentiles: 1%=0.000, 25%=0.000, 75%=1.000, 99%=4.000

📈 Shape Analysis:
   Skewness: 2.15 (Right-skewed)
   Kurtosis: 7.46 (Heavy tails/outliers)
   Zeros: 3,084 (61.7%)
   Outliers (IQR): 285 (5.7%)

🔧 Recommended Transformation: zero_inflation_handling
   Reason: Zero-inflation (61.7%) combined with high skewness (2.15)
   Priority: high
   ⚠️ Consider creating a binary indicator for zeros plus log transform of non-zero values

======================================================================
Column: send_hour_sum_180d
Type: numeric_continuous (Confidence: 90%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 8.645  |  Median: 0.000  |  Std: 13.953
   Range: [0.000, 149.000]
   Percentiles: 1%=0.000, 25%=0.000, 75%=15.000, 99%=56.030

📈 Shape Analysis:
   Skewness: 2.19 (Right-skewed)
   Kurtosis: 7.43 (Heavy tails/outliers)
   Zeros: 3,084 (61.7%)
   Outliers (IQR): 227 (4.5%)

🔧 Recommended Transformation: zero_inflation_handling
   Reason: Zero-inflation (61.7%) combined with high skewness (2.19)
   Priority: high
   ⚠️ Consider creating a binary indicator for zeros plus log transform of non-zero values

# Numerical Feature Statistics Table
if numeric_cols:
    stats_data = []
    for col_name in numeric_cols:
        series = df[col_name].dropna()
        if len(series) > 0:
            stats_data.append({
                "feature": col_name,
                "count": len(series),
                "mean": series.mean(),
                "std": series.std(),
                "min": series.min(),
                "25%": series.quantile(0.25),
                "50%": series.quantile(0.50),
                "75%": series.quantile(0.75),
                "95%": series.quantile(0.95),
                "99%": series.quantile(0.99),
                "max": series.max(),
                "skewness": stats.skew(series),
                "kurtosis": stats.kurtosis(series)
            })

    stats_df = pd.DataFrame(stats_data)

    # Format for display
    display_stats = stats_df.copy()
    for col in ["mean", "std", "min", "25%", "50%", "75%", "95%", "99%", "max"]:
        display_stats[col] = display_stats[col].apply(lambda x: f"{x:.3f}")
    display_stats["skewness"] = display_stats["skewness"].apply(lambda x: f"{x:.3f}")
    display_stats["kurtosis"] = display_stats["kurtosis"].apply(lambda x: f"{x:.3f}")

    print("=" * 80)
    print("NUMERICAL FEATURE STATISTICS")
    print("=" * 80)
    display(display_stats)

================================================================================
NUMERICAL FEATURE STATISTICS
================================================================================

# Build transformation summary table
summary_data = []
for col_name in numeric_cols:
    analysis = analyses.get(col_name)
    rec = recommendations.get(col_name)

    if analysis and rec:
        summary_data.append({
            "Column": col_name,
            "Skewness": f"{analysis.skewness:.2f}",
            "Kurtosis": f"{analysis.kurtosis:.2f}",
            "Zeros %": f"{analysis.zero_percentage:.1f}%",
            "Outliers %": f"{analysis.outlier_percentage:.1f}%",
            "Transform": rec.recommended_transform.value,
            "Priority": rec.priority
        })

        # Add Gold transformation recommendation if not "none"
        if rec.recommended_transform != TransformationType.NONE and registry.gold:
            registry.add_gold_transformation(
                column=col_name,
                transform=rec.recommended_transform.value,
                parameters=rec.parameters,
                rationale=rec.reason,
                source_notebook="04_column_deep_dive"
            )

if summary_data:
    summary_df = pd.DataFrame(summary_data)
    display_table(summary_df)

    # Show how many transformation recommendations were added
    transform_count = sum(1 for r in recommendations.values() if r and r.recommended_transform != TransformationType.NONE)
    if transform_count > 0 and registry.gold:
        print(f"\n✅ Added {transform_count} transformation recommendations to Gold layer")
else:
    console.info("No numeric columns to summarize")

✅ Added 159 transformation recommendations to Gold layer

# Use framework's CategoricalDistributionAnalyzer
cat_analyzer = CategoricalDistributionAnalyzer()

categorical_cols = [
    name for name, col in findings.columns.items()
    if col.inferred_type in [ColumnType.CATEGORICAL_NOMINAL, ColumnType.CATEGORICAL_ORDINAL, ColumnType.CATEGORICAL_CYCLICAL]
    and col.inferred_type != ColumnType.TEXT  # TEXT columns processed separately in 02a
    and name not in TEMPORAL_METADATA_COLS
]

# Analyze all categorical columns
cat_analyses = cat_analyzer.analyze_dataframe(df, categorical_cols)

# Get encoding recommendations
cyclical_cols = [name for name, col in findings.columns.items()
                 if col.inferred_type == ColumnType.CATEGORICAL_CYCLICAL]
cat_recommendations = cat_analyzer.get_all_recommendations(df, categorical_cols, cyclical_columns=cyclical_cols)

for col_name in categorical_cols:
    col_info = findings.columns[col_name]
    analysis = cat_analyses.get(col_name)
    rec = next((r for r in cat_recommendations if r.column_name == col_name), None)

    print(f"\n{'='*70}")
    print(f"Column: {col_name}")
    print(f"Type: {col_info.inferred_type.value} (Confidence: {col_info.confidence:.0%})")
    print("-" * 70)

    if analysis:
        print("\n📊 Distribution Metrics:")
        print(f"   Categories: {analysis.category_count}")
        print(f"   Imbalance Ratio: {analysis.imbalance_ratio:.1f}x (largest/smallest)")
        print(f"   Entropy: {analysis.entropy:.2f} ({analysis.normalized_entropy*100:.0f}% of max)")
        print(f"   Top-1 Concentration: {analysis.top1_concentration:.1f}%")
        print(f"   Top-3 Concentration: {analysis.top3_concentration:.1f}%")
        print(f"   Rare Categories (<1%): {analysis.rare_category_count}")

        # Interpretation
        print("\n📈 Interpretation:")
        if analysis.has_low_diversity:
            print("   ⚠️ LOW DIVERSITY: Distribution dominated by few categories")
        elif analysis.normalized_entropy > 0.9:
            print("   ✓ HIGH DIVERSITY: Categories are relatively balanced")
        else:
            print("   ✓ MODERATE DIVERSITY: Some category dominance but acceptable")

        if analysis.imbalance_ratio > 100:
            print("   🔴 SEVERE IMBALANCE: Rarest category has very few samples")
        elif analysis.is_imbalanced:
            print("   🟡 MODERATE IMBALANCE: Consider grouping rare categories")

        # Recommendations
        if rec:
            print("\n🔧 Recommendations:")
            print(f"   Encoding: {rec.encoding_type.value}")
            print(f"   Reason: {rec.reason}")
            print(f"   Priority: {rec.priority}")

            if rec.preprocessing_steps:
                print("   Preprocessing:")
                for step in rec.preprocessing_steps:
                    print(f"      • {step}")

            if rec.warnings:
                for warn in rec.warnings:
                    print(f"   ⚠️ {warn}")

    # Visualization
    value_counts = df[col_name].value_counts()
    subtitle = f"Entropy: {analysis.normalized_entropy*100:.0f}% | Imbalance: {analysis.imbalance_ratio:.1f}x | Rare: {analysis.rare_category_count}" if analysis else ""
    fig = charts.bar_chart(
        value_counts.head(10).index.tolist(),
        value_counts.head(10).values.tolist(),
        title=f"Top Categories: {col_name}<br><sub>{subtitle}</sub>"
    )
    display_figure(fig)

# Summary table and add recommendations to registry
if cat_analyses:
    print("\n" + "=" * 70)
    print("CATEGORICAL COLUMNS SUMMARY")
    print("=" * 70)
    summary_data = []
    for col_name, analysis in cat_analyses.items():
        rec = next((r for r in cat_recommendations if r.column_name == col_name), None)
        summary_data.append({
            "Column": col_name,
            "Categories": analysis.category_count,
            "Imbalance": f"{analysis.imbalance_ratio:.1f}x",
            "Entropy": f"{analysis.normalized_entropy*100:.0f}%",
            "Top-3 Conc.": f"{analysis.top3_concentration:.1f}%",
            "Rare (<1%)": analysis.rare_category_count,
            "Encoding": rec.encoding_type.value if rec else "N/A"
        })

        # Add encoding recommendation to Gold layer
        if rec and registry.gold:
            registry.add_gold_encoding(
                column=col_name,
                method=rec.encoding_type.value,
                rationale=rec.reason,
                source_notebook="04_column_deep_dive"
            )

    display_table(pd.DataFrame(summary_data))

    if registry.gold:
        print(f"\n✅ Added {len(cat_recommendations)} encoding recommendations to Gold layer")

======================================================================
Column: lifecycle_quadrant
Type: categorical_nominal (Confidence: 90%)
----------------------------------------------------------------------

📊 Distribution Metrics:
   Categories: 4
   Imbalance Ratio: 2.1x (largest/smallest)
   Entropy: 1.91 (96% of max)
   Top-1 Concentration: 33.7%
   Top-3 Concentration: 83.7%
   Rare Categories (<1%): 0

📈 Interpretation:
   ✓ HIGH DIVERSITY: Categories are relatively balanced

🔧 Recommendations:
   Encoding: one_hot
   Reason: Low cardinality (4 categories) - safe feature expansion
   Priority: low

======================================================================
Column: recency_bucket
Type: categorical_nominal (Confidence: 90%)
----------------------------------------------------------------------

📊 Distribution Metrics:
   Categories: 5
   Imbalance Ratio: 25.1x (largest/smallest)
   Entropy: 1.64 (71% of max)
   Top-1 Concentration: 61.7%
   Top-3 Concentration: 90.3%
   Rare Categories (<1%): 0

📈 Interpretation:
   ✓ MODERATE DIVERSITY: Some category dominance but acceptable
   🟡 MODERATE IMBALANCE: Consider grouping rare categories

🔧 Recommendations:
   Encoding: one_hot
   Reason: Low cardinality (5 categories) - safe feature expansion
   Priority: low
   ⚠️ Use stratified sampling to preserve rare category representation

======================================================================
CATEGORICAL COLUMNS SUMMARY
======================================================================

✅ Added 2 encoding recommendations to Gold layer

from customer_retention.stages.profiling.temporal_analyzer import TemporalRecommendationType

datetime_cols = [
    name for name, col in findings.columns.items()
    if col.inferred_type == ColumnType.DATETIME
    and name not in TEMPORAL_METADATA_COLS
]

temporal_analyzer = TemporalAnalyzer()

# Store all datetime recommendations grouped by type
feature_engineering_recs = []
modeling_strategy_recs = []
data_quality_recs = []
datetime_summaries = []

for col_name in datetime_cols:
    col_info = findings.columns[col_name]
    print(f"\n{'='*70}")
    print(f"Column: {col_name}")
    print(f"Type: {col_info.inferred_type.value} (Confidence: {col_info.confidence:.0%})")
    print(f"{'='*70}")

    date_series = pd.to_datetime(df[col_name], errors='coerce', format='mixed')
    valid_dates = date_series.dropna()

    print(f"\n📅 Date Range: {valid_dates.min()} to {valid_dates.max()}")
    print(f"   Nulls: {date_series.isna().sum():,} ({date_series.isna().mean()*100:.1f}%)")

    # Basic temporal analysis
    analysis = temporal_analyzer.analyze(date_series)
    print(f"   Auto-detected granularity: {analysis.granularity.value}")
    print(f"   Span: {analysis.span_days:,} days ({analysis.span_days/365:.1f} years)")

    # Growth analysis
    growth = temporal_analyzer.calculate_growth_rate(date_series)
    if growth.get("has_data"):
        print("\n📈 Growth Analysis:")
        print(f"   Trend: {growth['trend_direction'].upper()}")
        print(f"   Overall growth: {growth['overall_growth_pct']:+.1f}%")
        print(f"   Avg monthly growth: {growth['avg_monthly_growth']:+.1f}%")

    # Seasonality analysis
    seasonality = temporal_analyzer.analyze_seasonality(date_series)
    if seasonality.has_seasonality:
        print("\n🔄 Seasonality Detected:")
        print(f"   Peak months: {', '.join(seasonality.peak_periods[:3])}")
        print(f"   Trough months: {', '.join(seasonality.trough_periods[:3])}")
        print(f"   Seasonal strength: {seasonality.seasonal_strength:.2f}")

    # Get recommendations using framework
    other_dates = [c for c in datetime_cols if c != col_name]
    recommendations = temporal_analyzer.recommend_features(date_series, col_name, other_date_columns=other_dates)

    # Group by recommendation type
    col_feature_recs = [r for r in recommendations if r.recommendation_type == TemporalRecommendationType.FEATURE_ENGINEERING]
    col_modeling_recs = [r for r in recommendations if r.recommendation_type == TemporalRecommendationType.MODELING_STRATEGY]
    col_quality_recs = [r for r in recommendations if r.recommendation_type == TemporalRecommendationType.DATA_QUALITY]

    feature_engineering_recs.extend(col_feature_recs)
    modeling_strategy_recs.extend(col_modeling_recs)
    data_quality_recs.extend(col_quality_recs)

    # Display recommendations grouped by type
    if col_feature_recs:
        print("\n🛠️ FEATURES TO CREATE:")
        for rec in col_feature_recs:
            priority_icon = "🔴" if rec.priority == "high" else "🟡" if rec.priority == "medium" else "✓"
            print(f"   {priority_icon} {rec.feature_name} ({rec.category})")
            print(f"      Why: {rec.reason}")
            if rec.code_hint:
                print(f"      Code: {rec.code_hint}")

    if col_modeling_recs:
        print("\n⚙️ MODELING CONSIDERATIONS:")
        for rec in col_modeling_recs:
            priority_icon = "🔴" if rec.priority == "high" else "🟡" if rec.priority == "medium" else "✓"
            print(f"   {priority_icon} {rec.feature_name}")
            print(f"      Why: {rec.reason}")

    if col_quality_recs:
        print("\n⚠️ DATA QUALITY ISSUES:")
        for rec in col_quality_recs:
            priority_icon = "🔴" if rec.priority == "high" else "🟡" if rec.priority == "medium" else "✓"
            print(f"   {priority_icon} {rec.feature_name}")
            print(f"      Why: {rec.reason}")
            if rec.code_hint:
                print(f"      Code: {rec.code_hint}")

    # Standard extractions always available
    print("\n   Standard extractions available: year, month, day, day_of_week, quarter")

    # Store summary
    datetime_summaries.append({
        "Column": col_name,
        "Span (days)": analysis.span_days,
        "Seasonality": "Yes" if seasonality.has_seasonality else "No",
        "Trend": growth.get('trend_direction', 'N/A').capitalize() if growth.get("has_data") else "N/A",
        "Features to Create": len(col_feature_recs),
        "Modeling Notes": len(col_modeling_recs),
        "Quality Issues": len(col_quality_recs)
    })

    # === VISUALIZATIONS ===

    if growth.get("has_data"):
        fig = charts.growth_summary_indicators(growth, title=f"Growth Summary: {col_name}")
        display_figure(fig)

    chart_type = "line" if analysis.granularity in [TemporalGranularity.DAY, TemporalGranularity.WEEK] else "bar"
    fig = charts.temporal_distribution(analysis, title=f"Records Over Time: {col_name}", chart_type=chart_type)
    display_figure(fig)

    fig = charts.temporal_trend(analysis, title=f"Trend Analysis: {col_name}")
    display_figure(fig)

    yoy_data = temporal_analyzer.year_over_year_comparison(date_series)
    if len(yoy_data) > 1:
        fig = charts.year_over_year_lines(yoy_data, title=f"Year-over-Year: {col_name}")
        display_figure(fig)
        fig = charts.year_month_heatmap(yoy_data, title=f"Records Heatmap: {col_name}")
        display_figure(fig)

    if growth.get("has_data"):
        fig = charts.cumulative_growth_chart(growth["cumulative"], title=f"Cumulative Records: {col_name}")
        display_figure(fig)

    fig = charts.temporal_heatmap(date_series, title=f"Day of Week Distribution: {col_name}")
    display_figure(fig)

# === DATETIME SUMMARY ===
if datetime_summaries:
    print("\n" + "=" * 70)
    print("DATETIME COLUMNS SUMMARY")
    print("=" * 70)
    display_table(pd.DataFrame(datetime_summaries))

    # Summary by recommendation type
    print("\n📋 ALL RECOMMENDATIONS BY TYPE:")

    if feature_engineering_recs:
        print(f"\n🛠️ FEATURES TO CREATE ({len(feature_engineering_recs)}):")
        for i, rec in enumerate(feature_engineering_recs, 1):
            priority_icon = "🔴" if rec.priority == "high" else "🟡" if rec.priority == "medium" else "✓"
            print(f"   {i}. {priority_icon} {rec.feature_name}")

    if modeling_strategy_recs:
        print(f"\n⚙️ MODELING CONSIDERATIONS ({len(modeling_strategy_recs)}):")
        for i, rec in enumerate(modeling_strategy_recs, 1):
            priority_icon = "🔴" if rec.priority == "high" else "🟡" if rec.priority == "medium" else "✓"
            print(f"   {i}. {priority_icon} {rec.feature_name}: {rec.reason}")

    if data_quality_recs:
        print(f"\n⚠️ DATA QUALITY TO ADDRESS ({len(data_quality_recs)}):")
        for i, rec in enumerate(data_quality_recs, 1):
            priority_icon = "🔴" if rec.priority == "high" else "🟡" if rec.priority == "medium" else "✓"
            print(f"   {i}. {priority_icon} {rec.feature_name}: {rec.reason}")

    # Add recommendations to registry
    added_derived = 0
    added_modeling = 0

    # Add feature engineering recommendations to Silver layer (derived columns)
    if registry.silver:
        for rec in feature_engineering_recs:
            registry.add_silver_derived(
                column=rec.feature_name,
                expression=rec.code_hint or "",
                feature_type=rec.category,
                rationale=rec.reason,
                source_notebook="04_column_deep_dive"
            )
            added_derived += 1

    # Add modeling strategy recommendations to Bronze layer
    seen_strategies = set()
    for rec in modeling_strategy_recs:
        if rec.feature_name not in seen_strategies:
            registry.add_bronze_modeling_strategy(
                strategy=rec.feature_name,
                column=datetime_cols[0] if datetime_cols else "",
                parameters={"category": rec.category},
                rationale=rec.reason,
                source_notebook="04_column_deep_dive"
            )
            seen_strategies.add(rec.feature_name)
            added_modeling += 1

    print(f"\n✅ Added {added_derived} derived column recommendations to Silver layer")
    print(f"✅ Added {added_modeling} modeling strategy recommendations to Bronze layer")

# === TYPE OVERRIDES ===
# Uncomment and modify to override any incorrectly inferred types
TYPE_OVERRIDES = {
    # "column_name": ColumnType.NEW_TYPE,
    # Examples:
    # "is_active": ColumnType.BINARY,
    # "user_id": ColumnType.IDENTIFIER,
    # "satisfaction_level": ColumnType.CATEGORICAL_ORDINAL,
}

if TYPE_OVERRIDES:
    print("Applying type overrides:")
    for col_name, new_type in TYPE_OVERRIDES.items():
        if col_name in findings.columns:
            old_type = findings.columns[col_name].inferred_type.value
            findings.columns[col_name].inferred_type = new_type
            findings.columns[col_name].confidence = 1.0
            findings.columns[col_name].evidence.append("Manually overridden")
            print(f"  {col_name}: {old_type} → {new_type.value}")
else:
    print("No type overrides configured.")
    print("To override a type, add entries to TYPE_OVERRIDES dictionary above.")

No type overrides configured.
To override a type, add entries to TYPE_OVERRIDES dictionary above.

from customer_retention.stages.profiling import SegmentAnalyzer

# Initialize segment analyzer
segment_analyzer = SegmentAnalyzer()

# Find target column if detected
target_col = None
for col_name, col_info in findings.columns.items():
    if col_info.inferred_type == ColumnType.TARGET:
        target_col = col_name
        break

# Run segmentation analysis using numeric features
print("="*70)
print("DATA SEGMENTATION ANALYSIS")
print("="*70)

segmentation = segment_analyzer.analyze(
    df,
    target_col=target_col,
    feature_cols=numeric_cols if numeric_cols else None,
    max_segments=5
)

print("\n🎯 Analysis Results:")
print(f"   Method: {segmentation.method.value}")
print(f"   Detected Segments: {segmentation.n_segments}")
print(f"   Cluster Quality Score: {segmentation.quality_score:.2f}")
if segmentation.target_variance_ratio is not None:
    print(f"   Target Variance Ratio: {segmentation.target_variance_ratio:.2f}")

print("\n📊 Segment Profiles:")
for profile in segmentation.profiles:
    target_info = f" | Target Rate: {profile.target_rate*100:.1f}%" if profile.target_rate is not None else ""
    print(f"   Segment {profile.segment_id}: {profile.size:,} records ({profile.size_pct:.1f}%){target_info}")

# Display recommendation card
fig = charts.segment_recommendation_card(segmentation)
display_figure(fig)

# Display segment overview
fig = charts.segment_overview(segmentation, title="Segment Overview")
display_figure(fig)

# Display feature comparison if we have features
if segmentation.n_segments > 1 and any(p.defining_features for p in segmentation.profiles):
    fig = charts.segment_feature_comparison(segmentation, title="Feature Comparison Across Segments")
    display_figure(fig)

print("\n📝 Rationale:")
for reason in segmentation.rationale:
    print(f"   • {reason}")

======================================================================
DATA SEGMENTATION ANALYSIS
======================================================================

🎯 Analysis Results:
   Method: kmeans
   Detected Segments: 1
   Cluster Quality Score: 0.00
   Target Variance Ratio: 0.00

📊 Segment Profiles:
   Segment 0: 4,998 records (100.0%) | Target Rate: 44.6%

📝 Rationale:
   • Insufficient data for meaningful segmentation

# Save updated findings back to the same file
findings.save(FINDINGS_PATH)
print(f"Updated findings saved to: {FINDINGS_PATH}")

# Save recommendations registry
recommendations_path = FINDINGS_PATH.replace("_findings.yaml", "_recommendations.yaml")
registry.save(recommendations_path)
print(f"Recommendations saved to: {recommendations_path}")

# Summary of recommendations
all_recs = registry.all_recommendations
print("\n📋 Recommendations Summary:")
print(f"   Bronze layer: {len(registry.get_by_layer('bronze'))} recommendations")
print(f"   Silver layer: {len(registry.get_by_layer('silver'))} recommendations")
print(f"   Gold layer: {len(registry.get_by_layer('gold'))} recommendations")
print(f"   Total: {len(all_recs)} recommendations")

Updated findings saved to: /Users/Vital/python/CustomerRetention/experiments/runs/email-6301db6c/datasets/customer_emails/findings/customer_emails_aggregated_findings.yaml
Recommendations saved to: /Users/Vital/python/CustomerRetention/experiments/runs/email-6301db6c/datasets/customer_emails/findings/customer_emails_aggregated_recommendations.yaml

📋 Recommendations Summary:
   Bronze layer: 3 recommendations
   Silver layer: 0 recommendations
   Gold layer: 161 recommendations
   Total: 164 recommendations

Metric	Interpretation	Action
Skewness	Measures asymmetry	\|skew\| > 1: Consider log transform
Kurtosis	Measures tail heaviness	kurt > 10: Cap outliers before transform
Zero %	Percentage of zeros	> 40%: Use zero-inflation handling

Metric	Interpretation	Action
Imbalance Ratio	Largest / Smallest category count	> 10: Consider grouping rare categories
Entropy	Diversity measure (0 = one category, higher = more uniform)	Low entropy: May need stratified sampling
Top-3 Concentration	% of data in top 3 categories	> 90%: Rare categories may cause issues
Rare Category %	Categories with < 1% of data	High %: Group into "Other" category

Recommendation Type	Purpose	Examples
Feature Engineering	Create predictive features from dates	`days_since_signup`, `tenure_years`, `month_sin_cos`
Modeling Strategy	How to structure train/test	Time-based splits when trends detected
Data Quality	Issues to address before modeling	Placeholder dates (1/1/1900) to filter

Metric	Threshold	Action
Skewness	\|skew\| > 1	Log transform
Kurtosis	> 10	Cap outliers first
Zero %	> 40%	Zero-inflation handling

Metric	Threshold	Action
Imbalance Ratio	> 10x	Group rare categories
Entropy	< 50%	Stratified sampling
Rare Categories	> 0	Group into "Other"

Chapter 4: Column Deep Dive¶

4.1 Load Previous Findings¶

4.2 Load Source Data¶

4.3 Value Range Validation¶

VALUE RANGE VALIDATION¶

4.4 Numeric Columns Analysis¶

4.5 Distribution Summary & Transformation Plan¶

4.6 Categorical Columns Analysis¶

4.7 Datetime Columns Analysis¶

4.8 Type Override (Optional)¶

4.9 Data Segmentation Analysis¶

4.10 Save Updated Findings¶

Summary: What We Learned¶

Key Metrics Reference¶

Transformation & Encoding Summary¶

Next Steps¶

	feature	count	mean	std	min	25%	50%	75%	95%	99%	max	skewness	kurtosis
0	event_count_180d	4998	0.639	1.009	0.000	0.000	0.000	1.000	3.000	4.000	11.000	2.151	7.453
1	event_count_365d	4998	1.316	1.656	0.000	0.000	1.000	2.000	4.000	7.000	15.000	1.611	4.247
2	event_count_all_time	4998	16.566	9.139	1.000	12.000	16.000	19.000	30.000	53.000	112.000	2.612	16.094
3	opened_sum_180d	4998	0.152	0.424	0.000	0.000	0.000	0.000	1.000	2.000	4.000	3.141	11.507
4	opened_mean_180d	1914	0.232	0.366	0.000	0.000	0.000	0.500	1.000	1.000	1.000	1.267	0.042
...	...	...	...	...	...	...	...	...	...	...	...	...	...
174	bounced_vs_cohort_pct	4998	1.000	6.487	0.000	0.000	0.000	0.000	0.000	42.356	84.712	6.442	40.903
175	bounced_cohort_zscore	4998	0.000	1.000	-0.154	-0.154	-0.154	-0.154	-0.154	6.375	12.904	6.442	40.903
176	time_to_open_hours_vs_cohort_mean	4998	0.000	2.340	-0.689	-0.689	-0.689	-0.689	4.211	11.014	28.911	5.341	37.046
177	time_to_open_hours_vs_cohort_pct	4998	1.000	3.396	0.000	0.000	0.000	0.000	7.111	16.983	42.955	5.341	37.046
178	time_to_open_hours_cohort_zscore	4998	0.000	1.000	-0.294	-0.294	-0.294	-0.294	1.800	4.707	12.356	5.341	37.046

Column	Skewness	Kurtosis	Zeros %	Outliers %	Transform	Priority
event_count_180d	2.15	7.46	61.7%	5.7%	zero_inflation_handling	high
event_count_365d	1.61	4.25	47.3%	2.0%	zero_inflation_handling	medium
event_count_all_time	2.61	16.11	0.0%	6.0%	cap_then_log	high
opened_sum_180d	3.14	11.52	87.0%	13.0%	zero_inflation_handling	high
opened_mean_180d	1.27	0.05	66.1%	0.0%	zero_inflation_handling	medium
opened_count_180d	2.15	7.46	61.7%	5.7%	zero_inflation_handling	high
clicked_sum_180d	4.78	23.72	95.4%	4.6%	zero_inflation_handling	high
clicked_mean_180d	3.26	10.01	87.9%	12.1%	zero_inflation_handling	high
clicked_count_180d	2.15	7.46	61.7%	5.7%	zero_inflation_handling	high
send_hour_sum_180d	2.19	7.43	61.7%	4.5%	zero_inflation_handling	high
send_hour_mean_180d	0.04	-0.10	0.0%	0.0%	none	low
send_hour_max_180d	-0.17	-0.44	0.0%	0.0%	none	low
send_hour_count_180d	2.15	7.46	61.7%	5.7%	zero_inflation_handling	high
bounced_sum_180d	9.70	100.59	98.8%	1.2%	zero_inflation_handling	high
bounced_mean_180d	7.20	55.40	96.9%	3.1%	zero_inflation_handling	high
bounced_count_180d	2.15	7.46	61.7%	5.7%	zero_inflation_handling	high
time_to_open_hours_sum_180d	5.51	38.63	87.2%	12.8%	zero_inflation_handling	high
time_to_open_hours_mean_180d	1.90	5.01	1.1%	6.2%	sqrt_transform	medium
time_to_open_hours_max_180d	1.76	3.97	1.1%	6.0%	sqrt_transform	medium
time_to_open_hours_count_180d	3.14	11.52	87.0%	13.0%	zero_inflation_handling	high
opened_sum_365d	2.37	7.21	75.9%	24.1%	zero_inflation_handling	high
opened_mean_365d	1.19	0.43	54.3%	0.0%	zero_inflation_handling	medium
opened_count_365d	1.61	4.25	47.3%	2.0%	zero_inflation_handling	medium
clicked_sum_365d	3.48	12.93	90.9%	9.1%	zero_inflation_handling	high
clicked_mean_365d	3.06	10.07	82.8%	17.2%	zero_inflation_handling	high
clicked_count_365d	1.61	4.25	47.3%	2.0%	zero_inflation_handling	medium
send_hour_sum_365d	1.62	3.96	47.3%	2.2%	zero_inflation_handling	medium
send_hour_mean_365d	0.09	0.28	0.0%	2.4%	none	low
send_hour_max_365d	-0.34	-0.23	0.0%	0.0%	none	low
send_hour_count_365d	1.61	4.25	47.3%	2.0%	zero_inflation_handling	medium
bounced_sum_365d	6.95	56.96	97.5%	2.5%	zero_inflation_handling	high
bounced_mean_365d	6.70	52.39	95.2%	4.8%	zero_inflation_handling	high
bounced_count_365d	1.61	4.25	47.3%	2.0%	zero_inflation_handling	medium
time_to_open_hours_sum_365d	3.94	20.10	76.2%	23.8%	zero_inflation_handling	high
time_to_open_hours_mean_365d	2.25	9.46	1.1%	4.6%	yeo_johnson	high
time_to_open_hours_max_365d	1.90	6.19	1.1%	4.0%	sqrt_transform	medium
time_to_open_hours_count_365d	2.37	7.21	75.9%	24.1%	zero_inflation_handling	high
opened_sum_all_time	2.27	13.25	13.4%	2.1%	yeo_johnson	high
opened_mean_all_time	0.38	0.73	13.4%	0.7%	none	low
opened_count_all_time	2.61	16.11	0.0%	6.0%	cap_then_log	high
clicked_sum_all_time	2.00	8.18	39.4%	1.0%	zero_inflation_handling	high
clicked_mean_all_time	1.26	2.33	39.4%	1.0%	zero_inflation_handling	medium
clicked_count_all_time	2.61	16.11	0.0%	6.0%	cap_then_log	high
send_hour_sum_all_time	2.52	15.09	0.0%	5.1%	cap_then_log	high
send_hour_mean_all_time	-0.25	3.46	0.0%	3.2%	none	low
send_hour_max_all_time	-1.44	3.55	0.0%	2.4%	sqrt_transform	medium
send_hour_count_all_time	2.61	16.11	0.0%	6.0%	cap_then_log	high
bounced_sum_all_time	1.92	4.18	71.1%	1.1%	zero_inflation_handling	medium
bounced_mean_all_time	7.27	114.67	71.1%	4.8%	zero_inflation_handling	high
bounced_count_all_time	2.61	16.11	0.0%	6.0%	cap_then_log	high

Column	Categories	Imbalance	Entropy	Top-3 Conc.	Rare (<1%)	Encoding
lifecycle_quadrant	4	2.1x	96%	83.7%	0	one_hot
recency_bucket	5	25.1x	71%	90.3%	0	one_hot

Finding	Action
Seasonality	Add cyclical month encoding
Strong trend	Time-based train/test split
Multiple dates	Calculate duration features
Placeholder dates	Filter or flag