Py.Cafe

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

# --- 1. Load & Prep Data (Global Scope) ---
try:
    df = pd.read_excel("FactPerformance.xlsx", sheet_name="Sheet1")
except:
    # Mock data
    data = {
        'Score': [95, 82, 45, 60, 78, 88, 50, 92, 100, 72, 56, 30, 98, 85, 62],
        'Weight': [1, 1.2, 1, 1, 0.8, 1, 1, 1.5, 1, 1, 1, 1, 1, 1, 1]
    }
    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"
    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)
df = df.sort_values('Assessment_Grade')

# --- 2. State Management ---
# A reactive variable that works like a "Store" for the selected grade
selected_grade = solara.reactive("All")

# --- 3. UI Components ---

@solara.component
def KPIBox(title, value, color_class=""):
    with solara.Card(margin=0, classes=[color_class]):
        solara.Text(title, style="font-size: 14px; color: #666;")
        solara.Text(value, style="font-size: 24px; font-weight: bold;")

@solara.component
def Page():
    # 1. Filter Logic
    # Reactive variables are accessed via .value
    current_grade = selected_grade.value
    
    if current_grade == "All":
        dff = df
    else:
        dff = df[df["Assessment_Grade"] == current_grade]

    # 2. Calculate KPIs
    avg_score = f"{dff['Score'].mean():.2f}" if not dff.empty else "0.00"
    
    # Weighted Avg
    num = dff["WeightedScore"].sum()
    den = dff["Weight"].sum()
    w_avg = 0
    if den > 0:
        val = num / den
        if val <= 1.0 and val > 0: val *= 100
        w_avg = val
    weighted_disp = f"{w_avg:.2f}"

    # Pass Rate
    pass_count = dff[dff["PassedScore"] == "Pass"].shape[0]
    pass_rate = f"{(pass_count / len(dff) * 100):.2f}%" if not dff.empty else "0.00%"

    # 3. Define Plotly Click Handler
    def on_plot_click(click_data):
        # click_data comes directly from Solara's wrapper
        if click_data and "points" in click_data:
            # Get the label (Grade) from the clicked point
            grade = click_data["points"]["point_numbers"][0] 
            # Note: Plotly click data structure can vary slightly by chart type.
            # For Pie charts, we often get the label directly from 'label' or index
            # A safer way often involves inspecting the 'label' or 'x'/'y' depending on chart
            # For simplicity here, we assume standard trace access:
            point_index = click_data["points"]["point_indexes"][0]
            # We need to map this index back to the aggregated data used in the chart
            # (See chart generation below)
            clicked_label = df_agg.iloc[point_index]['Assessment_Grade']
            selected_grade.set(clicked_label)

    # 4. Create Chart
    # (We aggregate globally so the chart doesn't shrink when filtered)
    df_agg = df.groupby('Assessment_Grade', observed=False)['Score'].count().reset_index()
    df_agg.rename(columns={'Score': 'Count'}, inplace=True)
    
    fig = px.pie(
        df_agg, values='Count', names='Assessment_Grade', hole=0.6,
        color='Assessment_Grade',
        color_discrete_map={'A': '#2ca02c', 'B': '#1f77b4', 'C': '#ff7f0e', 'D': '#d62728', 'F': '#7f7f7f'}
    )
    fig.update_layout(clickmode='event+select')

    # --- 4. Render Layout ---
    with solara.Column(style={"padding": "20px"}):
        solara.Title("Education Performance Analysis (Solara)")
        
        # Reset Button & Status
        with solara.Row(style={"align-items": "center", "margin-bottom": "20px"}):
            solara.Text(f"Current Filter: {current_grade}", style="font-weight: bold; margin-right: 20px;")
            if current_grade != "All":
                solara.Button("Reset Filter", on_click=lambda: selected_grade.set("All"), color="primary")

        # KPI Row
        with solara.Columns([3, 3, 3, 3]):
            KPIBox("Average Score", avg_score)
            KPIBox("Weighted Avg", weighted_disp)
            KPIBox("Pass Rate", pass_rate)
            # (Simplified for brevity)
            KPIBox("Rows", str(len(dff)))

        # Charts & Data Row
        with solara.Columns([6, 6]):
            with solara.Card("Grade Distribution (Click Me)"):
                # NATIVE PLOTLY CLICK SUPPORT
                solara.FigurePlotly(fig, on_click=on_plot_click)
            
            with solara.Card("Score Detail"):
                solara.DataFrame(dff, items_per_page=10)