Py.Cafe

import solara
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go

# --- 1. Load Data & Pre-processing ---
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")

# Transformations
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"
    if score > 74: return "B"
    if score > 64: return "C"
    if score > 54: return "D"
    return "F"

df["Assessment_Grade"] = df["Score"].apply(get_grade)
grade_order = ['A', 'B', 'C', 'D', 'F']
df['Assessment_Grade'] = pd.Categorical(df['Assessment_Grade'], categories=grade_order, ordered=True)

if "GradeLevel" in df.columns:
    df = df.sort_values(['GradeLevel', 'Assessment_Grade'])


# --- 2. Helper Functions ---

def get_filtered_data(grade_filter, level_filter):
    d = df.copy()
    if grade_filter != "All":
        d = d[d["Assessment_Grade"] == grade_filter]
    if level_filter != "All":
        d = d[d["GradeLevel"] == level_filter]
    return d


# --- 3. Components ---

@solara.component
def ValueCard(title, value, bg_gradient=False, text_color=""):
    """Reusable KPI card component"""
    if bg_gradient:
        style = """
            background: linear-gradient(45deg, #6a11cb 0%, #2575fc 100%);
            color: white;
            padding: 20px;
            border-radius: 8px;
            text-align: center;
        """
    else:
        style = f"""
            padding: 20px;
            border-radius: 8px;
            border: 1px solid #ddd;
            text-align: center;
        """
    
    value_style = f"font-size: 24px; font-weight: bold; margin: 10px 0 0 0; color: {text_color};" if text_color else "font-size: 24px; font-weight: bold; margin: 10px 0 0 0;"
    
    with solara.Column(style=style):
        solara.HTML(tag="h6", unsafe_innerHTML=title, style="margin: 0; font-size: 14px;")
        solara.HTML(tag="h3", unsafe_innerHTML=value, style=value_style)


@solara.component
def ChartCard(title, figure):
    """Reusable chart card component"""
    with solara.Card(title, style="height: 100%;"):
        solara.FigurePlotly(figure)


@solara.component
def Page():
    # Reactive state variables
    selected_grade = solara.use_reactive("All")
    selected_level = solara.use_reactive("All")
    
    # Reset function
    def reset_filters():
        selected_grade.set("All")
        selected_level.set("All")
    
    # Get fully filtered data (intersection of both filters)
    fully_filtered = get_filtered_data(selected_grade.value, selected_level.value)
    
    # --- Calculate KPIs ---
    if fully_filtered.empty:
        kpi_avg = "0.00"
        kpi_weighted = "0.00%"
        kpi_pass = "0.00%"
        kpi_perfect = "0%"
    else:
        kpi_avg = f"{fully_filtered['Score'].mean():.2f}"
        
        w_sum = fully_filtered["Weight"].sum()
        if w_sum == 0:
            kpi_weighted = "0.00%"
        else:
            val = fully_filtered["WeightedScore"].sum() / w_sum
            if val <= 1.0 and val > 0: 
                val = val * 100
            kpi_weighted = f"{val:.2f}"
        
        pass_count = len(fully_filtered[fully_filtered['PassedScore']=='Pass'])
        kpi_pass = f"{(pass_count / len(fully_filtered) * 100):.2f}%"
        
        target = 100 if df["Score"].max() > 1.0 else 1.0
        perf_count = len(fully_filtered[fully_filtered['Score']==target])
        kpi_perfect = f"{(perf_count / len(fully_filtered) * 100):.1f}%"
    
    # --- Chart 1: Grade Distribution ---
    data_grade_slices = get_filtered_data("All", selected_level.value)
    df_agg_grade = data_grade_slices.groupby('Assessment_Grade', observed=False)['Score'].count().reset_index()
    df_agg_grade.rename(columns={'Score': 'Count'}, inplace=True)
    
    fig_grade = px.pie(
        df_agg_grade, 
        values='Count', 
        names='Assessment_Grade', 
        hole=0.6,
        color='Assessment_Grade', 
        color_discrete_map={'A': '#2ca02c', 'B': '#1f77b4', 'C': '#ff7f0e', 'D': '#d62728', 'F': '#7f7f7f'}
    )
    
    if selected_grade.value != "All":
        pull_list = [0.1 if x == selected_grade.value else 0 for x in df_agg_grade['Assessment_Grade']]
        fig_grade.update_traces(pull=pull_list)
    
    fig_grade.add_annotation(
        text=f"{len(fully_filtered):,}<br>Assessments",
        x=0.5, y=0.5, showarrow=False, font_size=16
    )
    fig_grade.update_layout(margin=dict(t=10, b=10, l=10, r=10))
    
    # --- Chart 2: Level Distribution ---
    data_level_slices = get_filtered_data(selected_grade.value, "All")
    df_agg_level = data_level_slices.groupby('GradeLevel', observed=False)['StudentID'].nunique().reset_index()
    df_agg_level.rename(columns={'StudentID': 'Count'}, inplace=True)
    
    fig_level = px.pie(
        df_agg_level, 
        values='Count', 
        names='GradeLevel', 
        hole=0.6,
        color='GradeLevel'
    )
    
    if selected_level.value != "All":
        pull_list = [0.1 if x == selected_level.value else 0 for x in df_agg_level['GradeLevel']]
        fig_level.update_traces(pull=pull_list)
    
    fig_level.add_annotation(
        text=f"{fully_filtered['StudentID'].nunique():,}<br>Students",
        x=0.5, y=0.5, showarrow=False, font_size=16
    )
    fig_level.update_layout(margin=dict(t=10, b=10, l=10, r=10))
    
    # --- Click handlers ---
    def on_grade_click(data):
        if data and 'points' in data and len(data['points']) > 0:
            clicked_val = data['points'][0]['label']
            if clicked_val == selected_grade.value:
                selected_grade.set("All")
            else:
                selected_grade.set(clicked_val)
    
    def on_level_click(data):
        if data and 'points' in data and len(data['points']) > 0:
            clicked_val = data['points'][0]['label']
            if clicked_val == selected_level.value:
                selected_level.set("All")
            else:
                selected_level.set(clicked_val)
    
    # --- Layout ---
    with solara.Column(style="padding: 20px; max-width: 1400px; margin: 0 auto;"):
        solara.HTML(tag="h2", unsafe_innerHTML="Education Performance Analysis", 
                   style="margin-bottom: 20px;")
        
        # Filter Status & Reset
        with solara.Row(justify="space-between", style="margin-bottom: 20px;"):
            solara.HTML(
                tag="h5",
                unsafe_innerHTML=f"Filters Active | Grade: {selected_grade.value} | Level: {selected_level.value}",
                style="color: #0066cc; margin: 0;"
            )
            solara.Button("Reset All Filters", on_click=reset_filters, color="primary")
        
        # KPI Cards
        with solara.Row(justify="space-between", style="margin-bottom: 20px; gap: 15px;"):
            with solara.Column(style="flex: 1;"):
                ValueCard("Average Score", kpi_avg, bg_gradient=True)
            with solara.Column(style="flex: 1;"):
                ValueCard("Weighted Avg", kpi_weighted, bg_gradient=True)
            with solara.Column(style="flex: 1;"):
                ValueCard("Pass Rate", kpi_pass, text_color="#28a745")
            with solara.Column(style="flex: 1;"):
                ValueCard("Perfect Scores", kpi_perfect, text_color="#007bff")
        
        # Charts
        with solara.Row(style="gap: 15px;"):
            with solara.Column(style="flex: 1;"):
                with solara.Card("Grade Distribution"):
                    solara.FigurePlotly(fig_grade, on_click=on_grade_click)
            with solara.Column(style="flex: 1;"):
                with solara.Card("Grade Level Distribution"):
                    solara.FigurePlotly(fig_level, on_click=on_level_click)