src/customer_intelligence/analytics/customers.py

"""Customer analytics queries."""

import pandas as pd
from sqlalchemy import Engine, text


def rfm_segment_distribution(engine: Engine) -> pd.DataFrame:
    sql = text("""
        SELECT rfm_segment, COUNT(*) AS customers
        FROM dim_customers
        WHERE rfm_segment IS NOT NULL
        GROUP BY 1 ORDER BY 2 DESC
    """)
    with engine.connect() as conn:
        return pd.read_sql(sql, conn)


def top_customers_by_revenue(engine: Engine, limit: int = 100) -> pd.DataFrame:
    sql = text("""
        SELECT customer_unique_id, city, state, total_orders, total_revenue,
               rfm_segment, churn_probability, segment_label
        FROM dim_customers
        WHERE total_revenue IS NOT NULL
        ORDER BY total_revenue DESC LIMIT :lim
    """)
    with engine.connect() as conn:
        return pd.read_sql(sql, conn, params={"lim": limit})


def customers_by_state(engine: Engine) -> pd.DataFrame:
    sql = text("""
        SELECT state, COUNT(*) AS customers, SUM(total_revenue) AS revenue
        FROM dim_customers
        WHERE state IS NOT NULL
        GROUP BY 1 ORDER BY 2 DESC
    """)
    with engine.connect() as conn:
        return pd.read_sql(sql, conn)


def customers_with_geo(engine: Engine, limit: int = 5000) -> pd.DataFrame:
    sql = text("""
        SELECT customer_unique_id, city, state, lat, lng,
               total_revenue, rfm_segment, churn_probability
        FROM dim_customers
        WHERE lat IS NOT NULL AND lng IS NOT NULL AND total_revenue IS NOT NULL
        ORDER BY total_revenue DESC LIMIT :lim
    """)
    with engine.connect() as conn:
        return pd.read_sql(sql, conn, params={"lim": limit})