src/customer_intelligence/api/routers/customers.py

"""Customer profile and order history endpoints."""

from fastapi import APIRouter, Depends, HTTPException, Query
from sqlalchemy import text
from sqlalchemy.orm import Session

from customer_intelligence.api.dependencies import get_db

router = APIRouter(prefix="/customers", tags=["customers"])


@router.get("/{customer_unique_id}")
def get_customer(customer_unique_id: str, db: Session = Depends(get_db)):
    row = db.execute(
        text("""
            SELECT
                customer_unique_id, customer_id, city, state,
                first_order_date, last_order_date,
                total_orders, total_revenue,
                clv_score, rfm_segment,
                churn_probability, segment_label
            FROM dim_customers
            WHERE customer_unique_id = :uid
            LIMIT 1
        """),
        {"uid": customer_unique_id},
    ).fetchone()

    if not row:
        raise HTTPException(status_code=404, detail="Customer not found")

    return dict(row._mapping)


@router.get("/")
def list_customers(
    state: str | None = Query(None),
    segment: str | None = Query(None),
    limit: int = Query(50, le=500),
    offset: int = Query(0, ge=0),
    db: Session = Depends(get_db),
):
    where_clauses = []
    params: dict = {"limit": limit, "offset": offset}

    if state:
        where_clauses.append("state = :state")
        params["state"] = state.upper()
    if segment:
        where_clauses.append("rfm_segment = :segment")
        params["segment"] = segment

    where_sql = ("WHERE " + " AND ".join(where_clauses)) if where_clauses else ""

    rows = db.execute(
        text(f"""
            SELECT customer_unique_id, city, state, total_orders, total_revenue,
                   rfm_segment, churn_probability, segment_label
            FROM dim_customers
            {where_sql}
            ORDER BY total_revenue DESC
            LIMIT :limit OFFSET :offset
        """),
        params,
    ).fetchall()

    total = db.execute(
        text(f"SELECT COUNT(*) FROM dim_customers {where_sql}"),
        {k: v for k, v in params.items() if k not in ("limit", "offset")},
    ).scalar()

    return {"total": total, "offset": offset, "limit": limit, "items": [dict(r._mapping) for r in rows]}


@router.get("/{customer_unique_id}/orders")
def get_customer_orders(
    customer_unique_id: str,
    limit: int = Query(20, le=100),
    db: Session = Depends(get_db),
):
    rows = db.execute(
        text("""
            SELECT
                f.order_id, f.order_date_id, f.order_status,
                f.price, f.freight_value, f.review_score,
                f.days_to_delivery, f.is_late,
                dp.category_name_en AS product_category
            FROM fact_orders f
            JOIN dim_customers dc ON f.customer_key = dc.customer_key
            LEFT JOIN dim_products dp ON f.product_key = dp.product_key
            WHERE dc.customer_unique_id = :uid
            ORDER BY f.order_date_id DESC
            LIMIT :limit
        """),
        {"uid": customer_unique_id, "limit": limit},
    ).fetchall()

    return {"customer_unique_id": customer_unique_id, "orders": [dict(r._mapping) for r in rows]}