src/customer_intelligence/features/delivery.py

"""Delivery performance features per customer."""

import pandas as pd
from sqlalchemy import Engine, text


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

    Returns a DataFrame with columns:
        customer_unique_id, customer_key, avg_delivery_delay,
        max_delivery_delay, late_order_count, complaint_rate
    """
    sql = text("""
        SELECT
            dc.customer_unique_id,
            AVG(f.delivery_delay_days)              AS avg_delivery_delay,
            MAX(f.delivery_delay_days)              AS max_delivery_delay,
            SUM(CASE WHEN f.is_late THEN 1 ELSE 0 END)  AS late_order_count,
            COUNT(DISTINCT f.order_id)              AS total_orders,
            AVG(CASE WHEN f.review_score <= 2 THEN 1.0 ELSE 0.0 END) AS complaint_rate
        FROM warehouse.fact_orders f
        JOIN warehouse.dim_customers dc ON f.customer_key = dc.customer_key
        WHERE f.delivery_delay_days IS NOT NULL
        GROUP BY dc.customer_unique_id
    """)

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

    df["late_rate"] = (df["late_order_count"] / df["total_orders"].clip(lower=1)).round(4)
    return df