src/customer_intelligence/features/clv.py

"""Customer Lifetime Value feature computation."""

import pandas as pd
from sqlalchemy import Engine, text


def compute_clv_features(engine: Engine) -> pd.DataFrame:
    """
    Compute CLV-related features per customer_unique_id.

    Returns a DataFrame with columns:
        customer_unique_id, customer_key, avg_order_value, order_count,
        tenure_days, avg_days_between_orders, predicted_clv
    """
    sql = text("""
        SELECT
            dc.customer_unique_id,
            COUNT(DISTINCT f.order_id)                          AS order_count,
            SUM(f.price + f.freight_value)                      AS total_revenue,
            AVG(f.price + f.freight_value)                      AS avg_order_value,
            MAX(f.order_date_id) - MIN(f.order_date_id)         AS tenure_days,
            AVG(f.review_score)                                 AS avg_review_score,
            AVG(f.days_to_delivery)                             AS avg_days_to_delivery,
            AVG(CASE WHEN f.is_late THEN 1.0 ELSE 0.0 END)     AS late_delivery_rate
        FROM warehouse.fact_orders f
        JOIN warehouse.dim_customers dc ON f.customer_key = dc.customer_key
        WHERE f.order_status NOT IN ('canceled', 'unavailable')
        GROUP BY dc.customer_unique_id
    """)

    with engine.connect() as conn:
        df = pd.read_sql(sql, conn)

    # Simple CLV estimate: avg_order_value * order_count * quality_factor
    # quality_factor reduces CLV for customers with many late deliveries
    df["tenure_days"] = df["tenure_days"].fillna(0).astype(int)
    df["quality_factor"] = (1.0 - df["late_delivery_rate"].fillna(0) * 0.3).clip(0.5, 1.0)
    df["avg_days_between_orders"] = (
        df["tenure_days"] / df["order_count"].clip(lower=1)
    )
    df["predicted_clv"] = (
        df["avg_order_value"] * df["order_count"] * df["quality_factor"]
    ).round(2)

    return df