overview_page.py

import streamlit as st
import pandas as pd
import pipeline
import send_orders
from pathlib import Path

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 data
st.info("Loading data...")
try:
    pred_shortage_risk   = load_from_db("SELECT * FROM shortage_risk_output")
    pred_demand_forecast = load_from_db("SELECT * FROM demand_forecast")
    rec_redistribution   = load_from_db("SELECT * FROM redistribution_recommendations")
    dim_medicine         = load_from_db("SELECT * FROM dim_medicine")
except Exception as e:
    st.error(f"Failed to load data from database: {str(e)}")
    st.stop()

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

st.markdown('<div class="section-card">', unsafe_allow_html=True)
st.title("PharmaPulse AI — Overview")
st.write("A clean dashboard for shortage risk, demand forecast, redistribution insights, and automation controls.")
st.markdown('</div>', unsafe_allow_html=True)

st.markdown('<div class="section-card">', unsafe_allow_html=True)
st.header("Automation Control")
max_emails = st.number_input("Max supplier emails to send", min_value=1, value=3, step=1, help="Stop sending after this many supplier emails have been sent.")
col1, col2, col3 = st.columns(3)
with col1:
    if st.button("Run automation pipeline now"):
        with st.spinner("Running automation pipeline..."):
            try:
                result = pipeline.run_automation(conn)
                st.success("Automation pipeline completed successfully!")
                st.write(f"**Result:** {result['message']}")
                st.write(f"**Counts:** {result['counts']}")
            except Exception as e:
                st.error(f"Automation failed: {e}")
with col2:
    if st.button("Send order emails now"):
        with st.spinner("Sending purchase order emails..."):
            try:
                email_result = send_orders.send_purchase_orders(max_messages=int(max_emails))
                st.success("Purchase order emails sent successfully!")
                st.write(email_result)
            except Exception as e:
                st.error(f"Email workflow failed: {e}")
with col3:
    if st.button("Run automation + emails"):
        with st.spinner("Running automation and sending emails..."):
            try:
                result = pipeline.run_automation(conn)
                email_result = send_orders.send_purchase_orders(max_messages=int(max_emails))
                st.success("Automation and email workflow completed successfully!")
                st.write(f"**Result:** {result['message']}")
                st.write(email_result)
            except Exception as e:
                st.error(f"Workflow failed: {e}")
st.markdown('</div>', unsafe_allow_html=True)

latest_insight = pipeline.load_latest_insight(conn)
if latest_insight:
    st.markdown('<div class="section-card">', unsafe_allow_html=True)
    st.header("Generative Insight")
    st.write(latest_insight["summary_text"])
    st.markdown('</div>', unsafe_allow_html=True)

st.success("Data loaded successfully!")

# Find columns
shortage_medicine_col   = find_col(pred_shortage_risk, ["medicine_id"])
shortage_branch_col     = find_col(pred_shortage_risk, ["branch_id"])
shortage_risk_level_col = find_col(pred_shortage_risk, ["risk_level"])
shortage_risk_score_col = find_col(pred_shortage_risk, ["risk_score"])
forecast_pred_col = find_col(pred_demand_forecast, ["predicted_demand_7d","pred_demand_7d","forecast_next_7d","forecast","y_pred"])
forecast_base_col = find_col(pred_demand_forecast, ["rolling_mean_7","units_sold","actual_units_sold","y_true"])
redist_transfer_qty_col = find_col(rec_redistribution, ["transfer_qty","amount"])
redist_medicine_col     = find_col(rec_redistribution, ["medicine_id"])
med_medicine_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"])

if any(c is None for c in [shortage_medicine_col, shortage_branch_col, shortage_risk_level_col]):
    st.error("shortage_risk_output table must have: medicine_id, branch_id, risk_level")
    st.write("Available columns:", pred_shortage_risk.columns.tolist())
    st.stop()
if redist_transfer_qty_col is None:
    st.error("redistribution_recommendations table must have: transfer_qty or amount")
    st.write("Available columns:", rec_redistribution.columns.tolist())
    st.stop()

# Process data
pred_shortage_risk[shortage_risk_level_col] = pred_shortage_risk[shortage_risk_level_col].astype(str).str.strip().str.lower()
if shortage_risk_score_col:
    pred_shortage_risk[shortage_risk_score_col] = pd.to_numeric(pred_shortage_risk[shortage_risk_score_col], errors="coerce")
rec_redistribution[redist_transfer_qty_col] = pd.to_numeric(rec_redistribution[redist_transfer_qty_col], errors="coerce")

high_risk_df        = pred_shortage_risk[pred_shortage_risk[shortage_risk_level_col] == "high"].copy()
high_risk_medicines = high_risk_df[shortage_medicine_col].nunique()
critical_branches   = high_risk_df[shortage_branch_col].nunique()
redistribution_count = len(rec_redistribution)
total_transfer_qty  = rec_redistribution[redist_transfer_qty_col].fillna(0).sum()
total_cases         = len(pred_shortage_risk)
high_risk_rate      = (len(high_risk_df) / total_cases * 100) if total_cases > 0 else 0

st.markdown('<div class="mini-badge">Executive Snapshot Layer</div>', unsafe_allow_html=True)
st.markdown("<h1>PharmaPulse AI — Overview</h1>", unsafe_allow_html=True)

c1,c2,c3,c4,c5,c6 = st.columns(6)
with c1: st.markdown(f'<div class="kpi-card"><div class="kpi-label">High-Risk Medicines</div><div class="kpi-value kpi-high">{high_risk_medicines:,}</div></div>',unsafe_allow_html=True)
with c2: st.markdown(f'<div class="kpi-card"><div class="kpi-label">Critical Branches</div><div class="kpi-value kpi-neutral">{critical_branches:,}</div></div>',unsafe_allow_html=True)
with c3: st.markdown(f'<div class="kpi-card"><div class="kpi-label">Redistribution Recs</div><div class="kpi-value kpi-neutral">{redistribution_count:,}</div></div>',unsafe_allow_html=True)
with c4: st.markdown(f'<div class="kpi-card"><div class="kpi-label">Total Transfer Qty</div><div class="kpi-value kpi-neutral">{int(total_transfer_qty):,}</div></div>',unsafe_allow_html=True)
with c5: st.markdown(f'<div class="kpi-card"><div class="kpi-label">High Risk Rate</div><div class="kpi-value kpi-warn">{high_risk_rate:.1f}%</div></div>',unsafe_allow_html=True)
with c6: st.markdown(f'<div class="kpi-card"><div class="kpi-value kpi-neutral">{total_cases:,}</div></div>',unsafe_allow_html=True)

st.markdown(f'<div class="subtext">Data processed successfully | High-risk rate: {high_risk_rate:.1f}%</div>',unsafe_allow_html=True)

# Show sample data
st.subheader("Sample High-Risk Data")
if not high_risk_df.empty:
    st.dataframe(high_risk_df.head(10))
else:
    st.warning("No high-risk data found")

st.subheader("Redistribution Recommendations")
if not rec_redistribution.empty:
    st.dataframe(rec_redistribution.head(10))
else:
    st.warning("No redistribution data found")