import streamlit as st
import pandas as pd
import plotly.express as px
import io

st.set_page_config(page_title="Excel → Management Insights", layout="wide")

st.title("📊 Excel → Interactive Management Dashboard")
st.caption("PASS if Grade ≥ C | FAIL if C- / D / F")

# -----------------------------
# Grade logic
# -----------------------------
def grade_pass_fail(g):
    if pd.isna(g):
        return "Unknown"
    g = str(g).strip().upper()
    if g.startswith(("D", "E", "F")):
        return "Fail"
    if g.startswith("C"):
        return "Fail" if g.startswith("C-") else "Pass"
    if g.startswith(("A", "B")):
        return "Pass"
    return "Unknown"


def normalize_headers(df):
    df.columns = [str(c).strip() for c in df.columns]
    return df


def pick_grade_column(df):
    for c in df.columns:
        if "grade" in c.lower():
            return c
    return df.columns[-1]


def ensure_sno(df):
    for c in df.columns:
        if c.lower() in ["sno", "id", "studentid"]:
            return df, c
    df.insert(0, "Sno", range(1, len(df) + 1))
    return df, "Sno"


# -----------------------------
# Upload section (CORRECT)
# -----------------------------
uploaded = st.file_uploader("Upload Excel (.xlsx)", type=["xlsx"])

file_bytes = None  # ✅ ALWAYS defined

if uploaded is None:
    st.info("Upload an Excel file to begin.")
    st.stop()

if st.button("✅ Load file"):
    try:
        file_bytes = uploaded.read()
    except Exception as e:
        st.error(f"Failed to read file: {e}")
        st.stop()

# ⛔ Stop if button not clicked or file not read
if file_bytes is None:
    st.warning("Click **Load file** after uploading.")
    st.stop()

if file_bytes == b"":
    st.error("Uploaded file is empty.")
    st.stop()

# -----------------------------
# Read Excel (force openpyxl)
# -----------------------------
try:
    bio = io.BytesIO(file_bytes)
    xls = pd.ExcelFile(bio, engine="openpyxl")
except Exception as e:
    st.error(f"Invalid Excel file. Please upload a real .xlsx file.\n\n{e}")
    st.stop()

sheet = st.selectbox("Select sheet", xls.sheet_names)

try:
    bio = io.BytesIO(file_bytes)
    raw = pd.read_excel(bio, sheet_name=sheet, engine="openpyxl")
except Exception as e:
    st.error(f"Cannot read selected sheet.\n\n{e}")
    st.stop()

raw = normalize_headers(raw)

# -----------------------------
# Build dataframe
# -----------------------------
grade_col = pick_grade_column(raw)
df = raw.copy()
df, sno_col = ensure_sno(df)

df["Grade"] = df[grade_col].astype(str).str.strip().str.upper()
df["PassFail"] = df["Grade"].apply(grade_pass_fail)
df["Pass"] = df["PassFail"] == "Pass"
df["Fail"] = df["PassFail"] == "Fail"

# -----------------------------
# KPIs
# -----------------------------
c1, c2, c3, c4 = st.columns(4)
c1.metric("Students", len(df))
c2.metric("Pass", int(df["Pass"].sum()))
c3.metric("Fail", int(df["Fail"].sum()))
c4.metric("Pass Rate", f"{df['Pass'].mean()*100:.1f}%")

st.divider()

# -----------------------------
# Views
# -----------------------------
view = st.sidebar.radio("View", ["Executive", "Risk", "Student", "Export"])

if view == "Executive":
    g = df["Grade"].value_counts().reset_index()
    g.columns = ["Grade", "Count"]
    st.plotly_chart(px.bar(g, x="Grade", y="Count"), use_container_width=True)

    p = df["PassFail"].value_counts().reset_index()
    p.columns = ["Status", "Count"]
    st.plotly_chart(px.pie(p, names="Status", values="Count"), use_container_width=True)

elif view == "Risk":
    st.subheader("Failing Students")
    st.dataframe(df[df["Fail"]][[sno_col, "Grade", "PassFail"]], use_container_width=True)

elif view == "Student":
    sid = st.selectbox("Select student", df[sno_col])
    st.dataframe(df[df[sno_col] == sid], use_container_width=True)

else:
    csv = df.to_csv(index=False).encode("utf-8")
    st.download_button("⬇️ Download CSV for Power BI", csv, "analysis_for_powerbi.csv", "text/csv")