Py.Cafe

import streamlit as st
import pandas as pd
import altair as alt

# --- 1. Load Data (Same logic) ---
@st.cache_data
def load_data():
    try:
        df_fact = pd.read_excel("FactPerformance.xlsx", sheet_name="Sheet1")
        df_dimStu = pd.read_excel("DimStudents.xlsx", sheet_name="Sheet1")
        df = pd.merge(df_fact, df_dimStu[["StudentID", "GradeLevel"]], on="StudentID", how="left")
    except:
        # Dummy Data
        data = {
            "StudentID": range(1, 101),
            "Score": [x % 100 for x in range(100)],
            "GradeLevel": ["Grade 1", "Grade 2", "Grade 3", "Grade 4"] * 25
        }
        df = pd.DataFrame(data)

    if "Weight" not in df.columns: df["Weight"] = 1
    if "WeightedScore" not in df.columns: df["WeightedScore"] = df["Score"] * df["Weight"]
    df["PassedScore"] = df["Score"].apply(lambda x: "Pass" if x >= 55 else "Fail")
    
    def get_grade(score):
        if score > 84: return "A"
        elif score > 74: return "B"
        elif score > 64: return "C"
        elif score > 54: return "D"
        else: return "F"
        
    df["Assessment_Grade"] = df["Score"].apply(get_grade)
    return df

df = load_data()

# --- 2. Session State for Filters ---
# We use session state to persist filters across reruns
if 'selected_grade' not in st.session_state:
    st.session_state.selected_grade = "All"
if 'selected_level' not in st.session_state:
    st.session_state.selected_level = "All"

# --- 3. Sidebar / Top Layout ---
st.set_page_config(layout="wide", page_title="Edu Dashboard")
st.title("Education Performance Analysis (Streamlit + Altair)")

col_status, col_reset = st.columns([8, 2])

with col_reset:
    if st.button("Reset All Filters", type="primary"):
        st.session_state.selected_grade = "All"
        st.session_state.selected_level = "All"
        st.rerun()

with col_status:
    st.caption(f"**Active Filters:** Grade: {st.session_state.selected_grade} | Level: {st.session_state.selected_level}")

# --- 4. Filter Logic ---
# Apply current filters to create the "KPI Dataset"
df_filtered = df.copy()
if st.session_state.selected_grade != "All":
    df_filtered = df_filtered[df_filtered["Assessment_Grade"] == st.session_state.selected_grade]
if st.session_state.selected_level != "All":
    df_filtered = df_filtered[df_filtered["GradeLevel"] == st.session_state.selected_level]

# --- 5. KPIs ---
kpi1, kpi2, kpi3, kpi4 = st.columns(4)

# KPI Helper
def safe_kpi(val, format_str="{:.2f}"):
    return format_str.format(val) if not pd.isna(val) else "0"

kpi1.metric("Average Score", safe_kpi(df_filtered['Score'].mean()))

weighted_avg = 0
if df_filtered['Weight'].sum() > 0:
    weighted_avg = df_filtered['WeightedScore'].sum() / df_filtered['Weight'].sum()
    if weighted_avg <= 1.0 and weighted_avg > 0: weighted_avg *= 100
kpi2.metric("Weighted Avg", safe_kpi(weighted_avg))

pass_rate = 0 if df_filtered.empty else len(df_filtered[df_filtered['PassedScore']=='Pass']) / len(df_filtered) * 100
kpi3.metric("Pass Rate", f"{pass_rate:.2f}%")

target = 100 if df["Score"].max() > 1.0 else 1.0
perfect_score = 0 if df_filtered.empty else len(df_filtered[df_filtered['Score']==target]) / len(df_filtered) * 100
kpi4.metric("Perfect Scores", f"{perfect_score:.1f}%")

st.divider()

# --- 6. Charts with Native Interactivity ---
c1, c2 = st.columns(2)

# --- CHART 1: GRADE DISTRIBUTION ---
with c1:
    st.subheader("Grade Distribution")
    
    # Context Data: Ignore Grade Filter, Respect Level Filter
    d_ctx = df.copy()
    if st.session_state.selected_level != "All":
        d_ctx = d_ctx[d_ctx["GradeLevel"] == st.session_state.selected_level]
    
    agg = d_ctx.groupby('Assessment_Grade', observed=False).size().reset_index(name='Count')
    
    # Colors
    domain = ['A', 'B', 'C', 'D', 'F']
    range_ = ['#2ca02c', '#1f77b4', '#ff7f0e', '#d62728', '#7f7f7f']
    
    # Define Selection
    select_grade = alt.selection_point(name="SelectGrade", fields=['Assessment_Grade'])
    
    base = alt.Chart(agg).encode(theta=alt.Theta("Count", stack=True))
    
    pie = base.mark_arc(innerRadius=60, outerRadius=100).encode(
        color=alt.Color("Assessment_Grade", scale=alt.Scale(domain=domain, range=range_)),
        order=alt.Order("Assessment_Grade"), # Simple sort works if categories are ordered in Pandas
        opacity=alt.condition(select_grade, alt.value(1), alt.value(0.3)),
        tooltip=["Assessment_Grade", "Count"]
    ).add_params(select_grade)
    
    text = alt.Chart(pd.DataFrame({'text': [str(len(df_filtered))]})).mark_text(
        size=30, fontWeight='bold'
    ).encode(text='text')

    # RENDER & CAPTURE SELECTION
    # on_select="rerun" creates the loop
    event_grade = st.altair_chart(pie + text, use_container_width=True, on_select="rerun")

    # Update State Logic
    if len(event_grade.selection['SelectGrade']) > 0:
        clicked_val = event_grade.selection['SelectGrade'][0]['Assessment_Grade']
        # Only update if it's a NEW change to avoid infinite loops
        if st.session_state.selected_grade != clicked_val:
            st.session_state.selected_grade = clicked_val
            st.rerun()

# --- CHART 2: LEVEL DISTRIBUTION ---
with c2:
    st.subheader("Grade Level Distribution")
    
    # Context Data: Ignore Level Filter, Respect Grade Filter
    d_ctx = df.copy()
    if st.session_state.selected_grade != "All":
        d_ctx = d_ctx[d_ctx["Assessment_Grade"] == st.session_state.selected_grade]
        
    agg = d_ctx.groupby('GradeLevel', observed=False).size().reset_index(name='Count')

    select_level = alt.selection_point(name="SelectLevel", fields=['GradeLevel'])
    
    base = alt.Chart(agg).encode(theta=alt.Theta("Count", stack=True))
    
    pie = base.mark_arc(innerRadius=60, outerRadius=100).encode(
        color=alt.Color("GradeLevel", scale=alt.Scale(scheme="category10")),
        opacity=alt.condition(select_level, alt.value(1), alt.value(0.3)),
        tooltip=["GradeLevel", "Count"]
    ).add_params(select_level)

    text = alt.Chart(pd.DataFrame({'text': [str(len(df_filtered))]})).mark_text(
        size=30, fontWeight='bold'
    ).encode(text='text')

    # RENDER & CAPTURE SELECTION
    event_level = st.altair_chart(pie + text, use_container_width=True, on_select="rerun")
    
    # Update State Logic
    if len(event_level.selection['SelectLevel']) > 0:
        clicked_val = event_level.selection['SelectLevel'][0]['GradeLevel']
        if st.session_state.selected_level != clicked_val:
            st.session_state.selected_level = clicked_val
            st.rerun()