src/customer_intelligence/analytics/revenue.py

"""Revenue analytics queries."""

import pandas as pd
from sqlalchemy import Engine, text


def monthly_revenue(engine: Engine) -> pd.DataFrame:
    sql = text("""
        SELECT
            date(strftime('%Y-%m-01', order_date_id)) AS month,
            SUM(price + freight_value) AS revenue,
            COUNT(DISTINCT order_id) AS orders,
            AVG(price + freight_value) AS avg_ticket
        FROM fact_orders
        WHERE order_status NOT IN ('canceled', 'unavailable')
          AND order_date_id IS NOT NULL
        GROUP BY 1 ORDER BY 1
    """)
    with engine.connect() as conn:
        return pd.read_sql(sql, conn)


def revenue_by_category(engine: Engine, top_n: int = 20) -> pd.DataFrame:
    sql = text("""
        SELECT
            COALESCE(dp.category_name_en, 'Unknown') AS category,
            SUM(f.price + f.freight_value) AS revenue,
            COUNT(DISTINCT f.order_id) AS orders
        FROM fact_orders f
        LEFT JOIN dim_products dp ON f.product_key = dp.product_key
        WHERE f.order_status NOT IN ('canceled', 'unavailable')
        GROUP BY 1 ORDER BY 2 DESC LIMIT :n
    """)
    with engine.connect() as conn:
        return pd.read_sql(sql, conn, params={"n": top_n})


def revenue_by_payment_type(engine: Engine) -> pd.DataFrame:
    sql = text("""
        SELECT payment_type, SUM(payment_value) AS revenue, COUNT(*) AS orders
        FROM fact_orders
        WHERE payment_type IS NOT NULL
        GROUP BY 1 ORDER BY 2 DESC
    """)
    with engine.connect() as conn:
        return pd.read_sql(sql, conn)


def order_status_distribution(engine: Engine) -> pd.DataFrame:
    sql = text("""
        SELECT order_status, COUNT(DISTINCT order_id) AS orders
        FROM fact_orders
        GROUP BY 1 ORDER BY 2 DESC
    """)
    with engine.connect() as conn:
        return pd.read_sql(sql, conn)


def kpi_summary(engine: Engine) -> dict:
    sql = text("""
        SELECT
            COUNT(DISTINCT order_id)        AS total_orders,
            SUM(price + freight_value)      AS total_revenue,
            AVG(price + freight_value)      AS avg_ticket,
            COUNT(DISTINCT customer_key)    AS active_customers
        FROM fact_orders
        WHERE order_status NOT IN ('canceled', 'unavailable')
    """)
    with engine.connect() as conn:
        row = conn.execute(sql).fetchone()
        return dict(row._mapping)