"""Customer segmentation page."""

import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent.parent.parent.parent.parent))

import pandas as pd
import streamlit as st
from sklearn.decomposition import PCA
from sqlalchemy import text

from customer_intelligence.analytics.customers import rfm_segment_distribution
from customer_intelligence.app.components.charts import make_bar_chart, make_scatter
from customer_intelligence.db import engine
from customer_intelligence.ml.segmentation import SEGMENT_NAMES

st.title("🔢 Customer Segmentation")

SEGMENT_FEATURES = [
    "recency_days", "frequency", "monetary",
    "avg_order_value", "tenure_days",
]


@st.cache_data(ttl=300)
def load_segment_data():
    sql = text("""
        SELECT
            dc.customer_unique_id,
            dc.segment_label,
            dc.rfm_segment,
            CAST(julianday(:today) - julianday(MAX(f.order_date_id)) AS INTEGER) AS recency_days,
            COUNT(DISTINCT f.order_id) AS frequency,
            SUM(f.price + f.freight_value) AS monetary,
            AVG(f.price + f.freight_value) AS avg_order_value,
            CAST(julianday(MAX(f.order_date_id)) - julianday(MIN(f.order_date_id)) AS INTEGER) AS tenure_days
        FROM fact_orders f
        JOIN dim_customers dc ON f.customer_key = dc.customer_key
        WHERE dc.segment_label IS NOT NULL
          AND f.order_status NOT IN ('canceled', 'unavailable')
        GROUP BY dc.customer_unique_id, dc.segment_label, dc.rfm_segment
        LIMIT 20000
    """)
    from datetime import date

    import pandas as pd
    with engine.connect() as conn:
        df = pd.read_sql(sql, conn, params={"today": str(date.today())})
    df[SEGMENT_FEATURES] = df[SEGMENT_FEATURES].apply(pd.to_numeric, errors="coerce")
    df = df.dropna(subset=SEGMENT_FEATURES)
    return df


df = load_segment_data()

segment_counts = (
    df.groupby("segment_label").size().reset_index(name="customers")
    .assign(segment_name=lambda x: x["segment_label"].map(SEGMENT_NAMES))
)

col1, col2 = st.columns(2)
with col1:
    st.plotly_chart(
        make_bar_chart(segment_counts, x="segment_name", y="customers", title="Cluster Sizes"),
        width="stretch",
    )
with col2:
    rfm_dist = rfm_segment_distribution(engine)
    st.plotly_chart(
        make_bar_chart(rfm_dist, x="rfm_segment", y="customers", title="RFM Segment Distribution"),
        width="stretch",
    )

st.subheader("2D PCA of Customer Clusters")
X = df[SEGMENT_FEATURES].values
pca = PCA(n_components=2, random_state=42)
coords = pca.fit_transform(X)
pca_df = pd.DataFrame({
    "PC1": coords[:, 0],
    "PC2": coords[:, 1],
    "Cluster": df["segment_label"].map(lambda x: SEGMENT_NAMES.get(x, f"Cluster {x}")),
})
st.plotly_chart(
    make_scatter(pca_df, x="PC1", y="PC2", color="Cluster",
                 title="Customer Clusters (PCA 2D)", opacity=0.4),
    width="stretch",
)

st.subheader("Segment Profiles")
profile = df.groupby("segment_label")[SEGMENT_FEATURES].mean().round(2)
profile.index = profile.index.map(lambda x: SEGMENT_NAMES.get(x, f"Cluster {x}"))
profile.index.name = "Segment"
st.dataframe(profile, width="stretch")