demand_forecast_page.py

import streamlit as st
import pandas as pd
import plotly.graph_objects as go
from pathlib import Path
import os

# Set Groq API key for AI insights


st.markdown("""<style>
    * { margin: 0; padding: 0; }
    body { background-color: #fafbfc !important; }
    .main { background-color: #fafbfc; }
    .block-container { padding-top: 2rem; padding-bottom: 2rem; padding-left: 2rem; padding-right: 2rem; }
    p, span, div { color: #1e293b !important; }
    h1, h2, h3, h4 { color: #0f172a; font-weight: 700; letter-spacing: -0.02em; margin-top: 0.5rem; margin-bottom: 0.5rem; }
    .subtext { color: #475569; font-size: 1rem; margin-top: 0.25rem; margin-bottom: 1rem; }
    .section-card { background: #ffffff; border: 1px solid #f0f4f8; border-radius: 18px; padding: 24px; box-shadow: 0 2px 8px rgba(15, 23, 42, 0.03); margin-bottom: 24px; }
    .kpi-card { background: linear-gradient(180deg, #ffffff 0%, #fafbfc 100%); border: 1px solid #f0f4f8; border-radius: 18px; padding: 20px; box-shadow: 0 2px 8px rgba(15, 23, 42, 0.03); min-height: 120px; }
    .kpi-label { color: #64748b; font-size: 0.95rem; margin-bottom: 8px; font-weight: 600; }
    .kpi-value { color: #0f172a; font-size: 2rem; font-weight: 800; line-height: 1.1; }
    .kpi-high { color: #dc2626; }
    .kpi-neutral { color: #0f172a; }
    .kpi-warn { color: #d97706; }
    .kpi-good { color: #16a34a; }
    .mini-badge { display: inline-block; padding: 8px 14px; border-radius: 999px; background: #f0f7ff; color: #1d4ed8; font-size: 0.85rem; font-weight: 600; border: 1px solid #e0f2fe; margin-bottom: 16px; }
    section[data-testid="stSidebar"] { background: #f5f8fa; border-right: 1px solid #f0f4f8; }
    #MainMenu { visibility: hidden !important; }
    [data-testid="stToolbar"] { display: none !important; }
    [data-testid="stDecoration"] { display: none !important; }
    .stWrite { color: #1e293b; }
    .stMarkdown { color: #1e293b; }
</style>""", unsafe_allow_html=True)

DB_PATH = Path(__file__).resolve().parent.parent / "pharma_pulse.db"

# Use session state connection from app.py
conn = st.session_state.get("db_conn")
if conn is None:
    st.error("Database connection not found. Please restart the application.")
    st.stop()

@st.cache_data
def load_from_db(query: str) -> pd.DataFrame:
    try:
        df = pd.read_sql_query(query, conn)
        return df
    except Exception as e:
        st.error(f"Database query failed: {str(e)}")
        return pd.DataFrame()

def find_col(df, candidates):
    lm = {c.lower(): c for c in df.columns}
    for c in candidates:
        if c.lower() in lm: return lm[c.lower()]
    return None

# Load from DB
try:
    mart_demand_features = load_from_db("SELECT * FROM mart_demand_features")
    pred_demand_forecast = load_from_db("SELECT * FROM demand_forecast")
    dim_medicine         = load_from_db("SELECT * FROM dim_medicine")
    dim_branch           = load_from_db("SELECT * FROM dim_branch")
except Exception as e:
    st.error(f"Failed to load data from database: {str(e)}")
    st.stop()

# Check if data loaded successfully
if mart_demand_features.empty or pred_demand_forecast.empty or dim_medicine.empty or dim_branch.empty:
    st.error("Failed to load required data from database. Please check database connection.")
    st.stop()

# Detect columns
actual_date_col     = find_col(mart_demand_features, ["date"])
actual_branch_col   = find_col(mart_demand_features, ["branch_id"])
actual_medicine_col = find_col(mart_demand_features, ["medicine_id"])
actual_value_col    = find_col(mart_demand_features, ["rolling_mean_7","units_sold"])

forecast_date_col     = find_col(pred_demand_forecast, ["forecast_date","date"])
forecast_branch_col   = find_col(pred_demand_forecast, ["branch_id"])
forecast_medicine_col = find_col(pred_demand_forecast, ["medicine_id"])
forecast_pred_col     = find_col(pred_demand_forecast, ["predicted_demand_7d","pred_demand_7d","forecast_next_7d","forecast","prediction","y_pred"])

med_id_col       = find_col(dim_medicine, ["medicine_id"])
med_generic_col  = find_col(dim_medicine, ["generic_name"])
med_brand_col    = find_col(dim_medicine, ["brand_name"])
med_category_col = find_col(dim_medicine, ["therapeutic_category","category"])

branch_id_col     = find_col(dim_branch, ["branch_id"])
branch_name_col   = find_col(dim_branch, ["branch_name"])
branch_region_col = find_col(dim_branch, ["region","governorate"])
branch_city_col   = find_col(dim_branch, ["city"])

if any(c is None for c in [actual_date_col, actual_branch_col, actual_medicine_col, actual_value_col]):
    st.error("mart_demand_features table must contain: date, branch_id, medicine_id, and units_sold or rolling_mean_7")
    st.write("Available columns:", mart_demand_features.columns.tolist())
    st.stop()
if any(c is None for c in [forecast_date_col, forecast_branch_col, forecast_medicine_col, forecast_pred_col]):
    st.error("demand_forecast table must contain: date, branch_id, medicine_id, and forecast_next_7d")
    st.write("Available columns:", pred_demand_forecast.columns.tolist())
    st.stop()

# Clean
mart_demand_features[actual_date_col]     = pd.to_datetime(mart_demand_features[actual_date_col], errors="coerce")
pred_demand_forecast[forecast_date_col]   = pd.to_datetime(pred_demand_forecast[forecast_date_col], errors="coerce")
mart_demand_features[actual_value_col]    = pd.to_numeric(mart_demand_features[actual_value_col], errors="coerce")
pred_demand_forecast[forecast_pred_col]   = pd.to_numeric(pred_demand_forecast[forecast_pred_col], errors="coerce")

actual_df   = mart_demand_features[[actual_date_col, actual_branch_col, actual_medicine_col, actual_value_col]].copy()
actual_df.columns = ["date","branch_id","medicine_id","actual_value"]
forecast_df = pred_demand_forecast[[forecast_date_col, forecast_branch_col, forecast_medicine_col, forecast_pred_col]].copy()
forecast_df.columns = ["forecast_date","branch_id","medicine_id","predicted_demand"]

# Build dimension tables
med_cols = [med_id_col] + ([med_generic_col] if med_generic_col else []) + ([med_brand_col] if med_brand_col else []) + ([med_category_col] if med_category_col else [])
medicine_df = dim_medicine[med_cols].drop_duplicates().copy()
rnm = {med_id_col:"medicine_id"}
if med_generic_col:  rnm[med_generic_col]  = "generic_name"
if med_brand_col:    rnm[med_brand_col]    = "brand_name"
if med_category_col: rnm[med_category_col] = "therapeutic_category"
medicine_df = medicine_df.rename(columns=rnm)

br_cols = [branch_id_col] + ([branch_name_col] if branch_name_col else []) + ([branch_region_col] if branch_region_col else []) + ([branch_city_col] if branch_city_col else [])
branch_df = dim_branch[br_cols].drop_duplicates().copy()
rnb = {branch_id_col:"branch_id"}
if branch_name_col:   rnb[branch_name_col]   = "branch_name"
if branch_region_col: rnb[branch_region_col] = "region"
if branch_city_col:   rnb[branch_city_col]   = "city"
branch_df = branch_df.rename(columns=rnb)

actual_joined   = actual_df.merge(medicine_df, on="medicine_id", how="left").merge(branch_df, on="branch_id", how="left")
forecast_joined = forecast_df.merge(medicine_df, on="medicine_id", how="left").merge(branch_df, on="branch_id", how="left")
actual_joined["plot_date"]   = actual_joined["date"]
forecast_joined["plot_date"] = forecast_joined["forecast_date"]
actual_joined   = actual_joined.sort_values("plot_date")
forecast_joined = forecast_joined.sort_values("plot_date")

# Sidebar filters
st.sidebar.header("Filters")
branch_display_col = "branch_name" if "branch_name" in actual_joined.columns else "branch_id"
medicine_display_col = "generic_name" if "generic_name" in actual_joined.columns else "medicine_id"
category_display_col = "therapeutic_category" if "therapeutic_category" in actual_joined.columns else None

branch_options = sorted(actual_joined[branch_display_col].dropna().unique().tolist()) if branch_display_col in actual_joined.columns else []
medicine_options = sorted(actual_joined[medicine_display_col].dropna().unique().tolist()) if medicine_display_col in actual_joined.columns else []
category_options = sorted(actual_joined[category_display_col].dropna().unique().tolist()) if category_display_col and category_display_col in actual_joined.columns else []

selected_branch = st.sidebar.selectbox("Branch", ["All"] + branch_options)
selected_medicine = st.sidebar.selectbox("Medicine", ["All"] + medicine_options)
selected_category = st.sidebar.selectbox("Category", ["All"] + category_options)

def apply_filters(df):
    out = df.copy()
    if selected_branch != "All" and branch_display_col in out.columns:
        out = out[out[branch_display_col] == selected_branch]
    if selected_medicine != "All" and medicine_display_col in out.columns:
        out = out[out[medicine_display_col] == selected_medicine]
    if selected_category != "All" and category_display_col and category_display_col in out.columns:
        out = out[out[category_display_col] == selected_category]
    return out

actual_filtered   = apply_filters(actual_joined)
forecast_filtered = apply_filters(forecast_joined)

actual_series   = actual_filtered.groupby("plot_date",as_index=False)["actual_value"].mean().sort_values("plot_date")
forecast_series = forecast_filtered.groupby("plot_date",as_index=False)["predicted_demand"].mean().sort_values("plot_date")

current_avg_sales = actual_filtered["actual_value"].mean() if not actual_filtered.empty else None
next_7d_forecast  = forecast_filtered["predicted_demand"].mean() if not forecast_filtered.empty else None
growth_pct = None
if current_avg_sales and not pd.isna(current_avg_sales) and current_avg_sales!=0 and next_7d_forecast and not pd.isna(next_7d_forecast):
    growth_pct = ((next_7d_forecast-current_avg_sales)/current_avg_sales)*100

growth_text = "N/A" if growth_pct is None or pd.isna(growth_pct) else ("0.0%" if abs(growth_pct)<0.05 else f"{growth_pct:.1f}%")

st.markdown('<div class="mini-badge">Forecasting Intelligence Layer</div>',unsafe_allow_html=True)
st.markdown("<h1>PharmaPulse AI — Demand Forecast</h1>",unsafe_allow_html=True)
st.markdown('<div class="subtext">Actual vs forecast demand trends by branch, medicine, and category.</div>',unsafe_allow_html=True)

c1,c2,c3=st.columns(3)
with c1: st.markdown(f'<div class="kpi-card"><div class="kpi-label">Current Avg Sales</div><div class="kpi-value kpi-neutral">{"N/A" if current_avg_sales is None or pd.isna(current_avg_sales) else f"{current_avg_sales:.1f}"}</div></div>',unsafe_allow_html=True)
with c2: st.markdown(f'<div class="kpi-card"><div class="kpi-label">Next 7D Forecast</div><div class="kpi-value kpi-good">{"N/A" if next_7d_forecast is None or pd.isna(next_7d_forecast) else f"{next_7d_forecast:.1f}"}</div></div>',unsafe_allow_html=True)
with c3:
    gc="kpi-good" if growth_pct is not None and not pd.isna(growth_pct) and growth_pct>=0 else "kpi-warn"
    st.markdown(f'<div class="kpi-card"><div class="kpi-label">Growth %</div><div class="kpi-value {gc}">{growth_text}</div></div>',unsafe_allow_html=True)

st.markdown('<div class="section-card">',unsafe_allow_html=True)
st.subheader("Actual vs Forecast Trend")
fig=go.Figure()
if not actual_series.empty:
    fig.add_trace(go.Scatter(x=actual_series["plot_date"],y=actual_series["actual_value"],mode="lines+markers",name="Actual",line=dict(width=3),marker=dict(size=5)))
if not forecast_series.empty:
    fig.add_trace(go.Scatter(x=forecast_series["plot_date"],y=forecast_series["predicted_demand"],mode="lines+markers",name="Forecast",line=dict(width=3,dash="dash"),marker=dict(size=5)))
fig.update_layout(xaxis_title="Date",yaxis_title="Demand / Units",hovermode="x unified",template="plotly_white",margin=dict(l=20,r=20,t=20,b=20),height=450)
st.plotly_chart(fig,use_container_width=True)
st.markdown('</div>',unsafe_allow_html=True)

st.markdown('<div class="section-card">',unsafe_allow_html=True)
st.subheader("Forecast Trend Area View")
fig_area=go.Figure()
if not forecast_series.empty:
    fig_area.add_trace(go.Scatter(x=forecast_series["plot_date"],y=forecast_series["predicted_demand"],fill="tozeroy",mode="lines",name="Forecast Area",line=dict(width=2)))
fig_area.update_layout(xaxis_title="Date",yaxis_title="Predicted Demand",hovermode="x unified",template="plotly_white",margin=dict(l=20,r=20,t=20,b=20),height=380)
st.plotly_chart(fig_area,use_container_width=True)
st.markdown('</div>',unsafe_allow_html=True)

st.markdown('<div class="section-card">',unsafe_allow_html=True)
st.subheader("Forecast Summary Table")
summary_df=pd.DataFrame([{"Branch":selected_branch,"Medicine":selected_medicine,"Category":selected_category,
    "Current Avg Sales":None if current_avg_sales is None or pd.isna(current_avg_sales) else round(current_avg_sales,2),
    "Next 7D Forecast":None if next_7d_forecast is None or pd.isna(next_7d_forecast) else round(next_7d_forecast,2),
    "Growth %":None if growth_pct is None or pd.isna(growth_pct) else round(growth_pct,2)}])
st.dataframe(summary_df,use_container_width=True,hide_index=True)
st.markdown('</div>',unsafe_allow_html=True)

# AI-Powered Medicine Insights
st.markdown('<div class="section-card">',unsafe_allow_html=True)
st.subheader("AI Analysis for Selected Medicine")
st.markdown("_Groq LLM powered deep analysis of supply chain risk and recommendations_")

try:
    import pipeline

    if selected_medicine and selected_medicine != "All" and selected_branch and selected_branch != "All":
        # Resolve selected medicine id or generic name
        medicine_id = None
        if medicine_display_col == "medicine_id":
            medicine_id = str(selected_medicine)
        else:
            med_row = medicine_df[medicine_df["generic_name"] == selected_medicine]
            if not med_row.empty:
                medicine_id = str(med_row.iloc[0]["medicine_id"])
            elif selected_medicine in medicine_df["medicine_id"].astype(str).values:
                medicine_id = str(selected_medicine)

        # Resolve selected branch id or name
        branch_id = None
        if branch_display_col == "branch_id":
            branch_id = str(selected_branch)
        else:
            branch_row = branch_df[branch_df["branch_name"] == selected_branch]
            if not branch_row.empty:
                branch_id = str(branch_row.iloc[0]["branch_id"])
            elif selected_branch in branch_df["branch_id"].astype(str).values:
                branch_id = str(selected_branch)

        if medicine_id is not None and branch_id is not None:
            medicine_id = str(medicine_id)
            branch_id = str(branch_id)
            risk_df = pd.read_sql_query(
                "SELECT * FROM shortage_risk_output WHERE medicine_id = ? AND branch_id = ? LIMIT 1",
                conn,
                params=(medicine_id, branch_id)
            )

            if not risk_df.empty:
                risk_row = risk_df.iloc[0].to_dict()
                insight = pipeline.generate_medicine_insight(risk_row)
                st.write(insight)
            else:
                st.info(f"No risk data found for {selected_medicine} in {selected_branch}")
        else:
            st.info("Please select specific medicine and branch (not 'All')")
    else:
        st.info("Please select specific medicine and branch (not 'All') to view AI analysis")
except Exception as e:
    st.warning(f"AI analysis temporarily unavailable: {str(e)}")

st.markdown('</div>',unsafe_allow_html=True)

with st.expander("Debug / data status"):
    st.write({"actual_shape":actual_df.shape,"forecast_shape":forecast_df.shape,"actual_value_col":actual_value_col,"forecast_pred_col":forecast_pred_col})