import streamlit as st
import pandas as pd
import sqlite3
import random
import hashlib
import os
from datetime import datetime, date

st.set_page_config(page_title="School Management App", layout="wide")

# -----------------------------
# Constants (from your original)
# -----------------------------
CLASSES = [f"Class {i}" for i in range(1, 13)]
SUBJECTS = {
    "Class 1": ["Bangla", "English", "Math"],
    "Class 2": ["Bangla", "English", "Math"],
    "Class 3": ["Bangla", "English", "Math", "Science"],
    "Class 4": ["Bangla", "English", "Math", "Science"],
    "Class 5": ["Bangla", "English", "Math", "Science"],
    "Class 6": ["Bangla", "English", "Math", "Science", "ICT"],
    "Class 7": ["Bangla", "English", "Math", "Science", "ICT"],
    "Class 8": ["Bangla", "English", "Math", "Science", "ICT"],
    "Class 9": ["Bangla", "English", "Math", "Physics", "Chemistry", "Biology"],
    "Class 10": ["Bangla", "English", "Math", "Physics", "Chemistry", "Biology"],
    "Class 11": ["Bangla", "English", "Math", "Physics", "Chemistry"],
    "Class 12": ["Bangla", "English", "Math", "Physics", "Chemistry"]
}

FIRST_NAMES = ["Ahsan", "Rahim", "Karim", "Sadia", "Nabila", "Hasan", "Rafi", "Tamim", "Mim", "Nusrat"]
LAST_NAMES = ["Ahmed", "Islam", "Hossain", "Khan", "Rahman", "Ali"]

DB_PATH = "school.db"

# -----------------------------
# DB Helpers
# -----------------------------
def db():
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    return conn

def run_sql(q, params=(), fetch=False, many=False):
    conn = db()
    cur = conn.cursor()
    if many:
        cur.executemany(q, params)
    else:
        cur.execute(q, params)
    conn.commit()
    rows = cur.fetchall() if fetch else None
    conn.close()
    return rows

def make_salt():
    return os.urandom(16).hex()

def hash_pw(password: str, salt: str) -> str:
    return hashlib.sha256((salt + password).encode("utf-8")).hexdigest()

def init_db():
    run_sql("""
    CREATE TABLE IF NOT EXISTS students(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        class_name TEXT NOT NULL
    )""")

    run_sql("""
    CREATE TABLE IF NOT EXISTS users(
        username TEXT PRIMARY KEY,
        salt TEXT NOT NULL,
        pw_hash TEXT NOT NULL,
        role TEXT NOT NULL,            -- admin / teacher / student
        student_id INTEGER,            -- if role=student
        FOREIGN KEY(student_id) REFERENCES students(id)
    )""")

    run_sql("""
    CREATE TABLE IF NOT EXISTS homework(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        class_name TEXT NOT NULL,
        subject TEXT NOT NULL,
        title TEXT NOT NULL,
        description TEXT NOT NULL,
        due_date TEXT,                 -- ISO date
        created_at TEXT NOT NULL
    )""")

    run_sql("""
    CREATE TABLE IF NOT EXISTS submissions(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        homework_id INTEGER NOT NULL,
        student_id INTEGER NOT NULL,
        submitted_at TEXT NOT NULL,
        text_answer TEXT,
        file_name TEXT,
        file_bytes BLOB,
        FOREIGN KEY(homework_id) REFERENCES homework(id),
        FOREIGN KEY(student_id) REFERENCES students(id)
    )""")

    run_sql("""
    CREATE TABLE IF NOT EXISTS marks(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        student_id INTEGER NOT NULL,
        class_name TEXT NOT NULL,
        exam TEXT NOT NULL,            -- e.g., Midterm, Final
        subject TEXT NOT NULL,
        marks REAL NOT NULL,
        total REAL NOT NULL,
        entered_at TEXT NOT NULL,
        FOREIGN KEY(student_id) REFERENCES students(id)
    )""")

    run_sql("""
    CREATE TABLE IF NOT EXISTS attendance(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        class_name TEXT NOT NULL,
        att_date TEXT NOT NULL,        -- ISO date
        student_id INTEGER NOT NULL,
        status TEXT NOT NULL,          -- Present / Absent
        FOREIGN KEY(student_id) REFERENCES students(id)
    )""")

def seed_demo_data():
    # Seed admin/teacher if missing
    rows = run_sql("SELECT COUNT(*) AS c FROM users", fetch=True)
    if rows[0]["c"] == 0:
        for username, password, role in [
            ("admin", "admin123", "admin"),
            ("teacher", "teacher123", "teacher"),
        ]:
            salt = make_salt()
            run_sql(
                "INSERT INTO users(username,salt,pw_hash,role,student_id) VALUES(?,?,?,?,NULL)",
                (username, salt, hash_pw(password, salt), role)
            )

    # Seed students + student accounts if missing
    srows = run_sql("SELECT COUNT(*) AS c FROM students", fetch=True)
    if srows[0]["c"] == 0:
        demo_students = []
        # Create 10 students per class (adjust as you like)
        for cls in CLASSES:
            for _ in range(10):
                name = f"{random.choice(FIRST_NAMES)} {random.choice(LAST_NAMES)}"
                demo_students.append((name, cls))
        run_sql("INSERT INTO students(name,class_name) VALUES(?,?)", demo_students, many=True)

        # Create login users for first 30 students
        students = run_sql("SELECT id FROM students ORDER BY id LIMIT 30", fetch=True)
        for s in students:
            sid = s["id"]
            username = f"s{1000 + sid}"
            password = "student123"
            salt = make_salt()
            run_sql(
                "INSERT OR REPLACE INTO users(username,salt,pw_hash,role,student_id) VALUES(?,?,?,?,?)",
                (username, salt, hash_pw(password, salt), "student", sid)
            )

def get_user(username):
    rows = run_sql("SELECT * FROM users WHERE username=?", (username,), fetch=True)
    return rows[0] if rows else None

def get_student(student_id):
    rows = run_sql("SELECT * FROM students WHERE id=?", (student_id,), fetch=True)
    return rows[0] if rows else None

def require_login():
    return "user" in st.session_state and st.session_state.user is not None

# -----------------------------
# Boot
# -----------------------------
init_db()
seed_demo_data()

# -----------------------------
# Auth UI
# -----------------------------
def login_box():
    st.sidebar.subheader("🔐 Login")
    u = st.sidebar.text_input("Username", key="login_u")
    p = st.sidebar.text_input("Password", type="password", key="login_p")
    if st.sidebar.button("Login", use_container_width=True):
        user = get_user(u.strip())
        if not user:
            st.sidebar.error("Invalid username/password")
            return
        if hash_pw(p, user["salt"]) != user["pw_hash"]:
            st.sidebar.error("Invalid username/password")
            return
        st.session_state.user = dict(user)
        st.rerun()

def logout_box():
    st.sidebar.success(f"Logged in as: {st.session_state.user['username']} ({st.session_state.user['role']})")
    if st.sidebar.button("Logout", use_container_width=True):
        st.session_state.user = None
        st.rerun()

if "user" not in st.session_state:
    st.session_state.user = None

# -----------------------------
# UI Header
# -----------------------------
st.title("🏫 School Management App (Login + Homework + Results + Attendance)")

# Sidebar auth
if not require_login():
    login_box()
    st.info("Use demo logins: admin/admin123, teacher/teacher123, student s1001/student123")
    st.stop()
else:
    logout_box()

role = st.session_state.user["role"]
student_id = st.session_state.user.get("student_id")

# -----------------------------
# Common helpers for pages
# -----------------------------
def grade_from_pct(pct: float) -> str:
    if pct >= 80: return "A+"
    if pct >= 70: return "A"
    if pct >= 60: return "A-"
    if pct >= 50: return "B"
    if pct >= 40: return "C"
    if pct >= 33: return "D"
    return "F"

def teacher_or_admin():
    return role in ("teacher", "admin")

# -----------------------------
# Navigation by role
# -----------------------------
if role == "student":
    nav = st.sidebar.radio("📌 Menu", ["Home", "Homework", "My Submissions", "Attendance", "Results", "Profile"])
else:
    nav = st.sidebar.radio("📌 Menu", ["Home", "Homework (Create)", "Submissions (View)", "Attendance (Take)", "Marks (Enter)", "Results (View)", "Students/Users"])

# -----------------------------
# STUDENT PAGES
# -----------------------------
if role == "student":
    s = get_student(student_id)
    if not s:
        st.error("Student profile not found. Ask admin to fix your account.")
        st.stop()

    cls = s["class_name"]

    if nav == "Home":
        st.subheader("🏠 Student Dashboard")
        st.write(f"**Name:** {s['name']}")
        st.write(f"**Class:** {cls}")

        # Upcoming homework
        hws = run_sql(
            "SELECT * FROM homework WHERE class_name=? ORDER BY created_at DESC LIMIT 10",
            (cls,), fetch=True
        )
        st.markdown("### 📚 Latest Homework")
        if not hws:
            st.info("No homework posted yet.")
        else:
            df = pd.DataFrame([dict(r) for r in hws])[["subject", "title", "due_date", "created_at"]]
            st.dataframe(df, use_container_width=True)

    elif nav == "Homework":
        st.subheader("📚 Homework")
        hws = run_sql(
            "SELECT * FROM homework WHERE class_name=? ORDER BY created_at DESC",
            (cls,), fetch=True
        )
        if not hws:
            st.info("No homework for your class yet.")
        else:
            hw_map = {f"#{r['id']} | {r['subject']} | {r['title']} (Due: {r['due_date'] or 'N/A'})": r for r in hws}
            pick = st.selectbox("Select Homework", list(hw_map.keys()))
            hw = hw_map[pick]

            st.markdown(f"### {hw['title']}")
            st.write(f"**Subject:** {hw['subject']}")
            st.write(f"**Due date:** {hw['due_date'] or 'N/A'}")
            st.write(hw["description"])

            st.markdown("### ✅ Submit Homework")
            # Check if already submitted
            existing = run_sql(
                "SELECT * FROM submissions WHERE homework_id=? AND student_id=? ORDER BY submitted_at DESC LIMIT 1",
                (hw["id"], student_id), fetch=True
            )
            if existing:
                st.warning(f"You already submitted on {existing[0]['submitted_at']}. Submitting again will create another entry.")

            ans = st.text_area("Write your answer (optional)")
            up = st.file_uploader("Upload file (optional)", type=None)

            if st.button("Submit", use_container_width=True):
                file_name, file_bytes = None, None
                if up is not None:
                    file_name = up.name
                    file_bytes = up.getvalue()

                run_sql(
                    """INSERT INTO submissions(homework_id,student_id,submitted_at,text_answer,file_name,file_bytes)
                       VALUES(?,?,?,?,?,?)""",
                    (hw["id"], student_id, datetime.now().isoformat(timespec="seconds"), ans, file_name, file_bytes)
                )
                st.success("Submitted successfully!")
                st.rerun()

    elif nav == "My Submissions":
        st.subheader("🗂️ My Submissions")
        rows = run_sql("""
            SELECT s.id AS sub_id, s.submitted_at, s.text_answer, s.file_name,
                   h.subject, h.title, h.class_name, h.due_date
            FROM submissions s
            JOIN homework h ON h.id = s.homework_id
            WHERE s.student_id=?
            ORDER BY s.submitted_at DESC
        """, (student_id,), fetch=True)

        if not rows:
            st.info("No submissions yet.")
        else:
            df = pd.DataFrame([dict(r) for r in rows])
            st.dataframe(df[["sub_id","submitted_at","class_name","subject","title","due_date","file_name"]], use_container_width=True)

            sub_ids = df["sub_id"].tolist()
            pick_id = st.selectbox("Select submission to view/download", sub_ids)
            one = run_sql("SELECT * FROM submissions WHERE id=?", (pick_id,), fetch=True)[0]
            st.write("**Submitted at:**", one["submitted_at"])
            st.write("**Text Answer:**")
            st.code(one["text_answer"] or "")

            if one["file_bytes"] is not None:
                st.download_button(
                    "Download attached file",
                    data=one["file_bytes"],
                    file_name=one["file_name"] or "submission.bin",
                    use_container_width=True
                )

    elif nav == "Attendance":
        st.subheader("🕒 Attendance")
        rows = run_sql("""
            SELECT att_date, status
            FROM attendance
            WHERE student_id=?
            ORDER BY att_date DESC
        """, (student_id,), fetch=True)

        if not rows:
            st.info("No attendance records found yet.")
        else:
            df = pd.DataFrame([dict(r) for r in rows])
            present = (df["status"] == "Present").sum()
            total = len(df)
            st.metric("Attendance %", f"{(present/total)*100:.1f}%")
            st.dataframe(df, use_container_width=True)

    elif nav == "Results":
        st.subheader("📄 Result Sheet")
        exams = run_sql("""
            SELECT DISTINCT exam FROM marks
            WHERE student_id=? AND class_name=?
            ORDER BY exam
        """, (student_id, cls), fetch=True)

        if not exams:
            st.info("No marks entered yet.")
        else:
            exam_list = [r["exam"] for r in exams]
            exam = st.selectbox("Select Exam", exam_list)

            rows = run_sql("""
                SELECT subject, marks, total, entered_at
                FROM marks
                WHERE student_id=? AND class_name=? AND exam=?
                ORDER BY subject
            """, (student_id, cls, exam), fetch=True)

            df = pd.DataFrame([dict(r) for r in rows])
            df["%"] = (df["marks"] / df["total"] * 100).round(2)
            df["Grade"] = df["%"].apply(grade_from_pct)

            st.dataframe(df[["subject","marks","total","%","Grade","entered_at"]], use_container_width=True)

            total_marks = df["marks"].sum()
            total_outof = df["total"].sum()
            pct = (total_marks / total_outof * 100) if total_outof else 0
            st.metric("Total", f"{total_marks:.0f}/{total_outof:.0f}")
            st.metric("Overall %", f"{pct:.2f}%")
            st.metric("Overall Grade", grade_from_pct(pct))

    elif nav == "Profile":
        st.subheader("👤 Profile")
        st.write(f"**Student ID:** {student_id}")
        st.write(f"**Name:** {s['name']}")
        st.write(f"**Class:** {s['class_name']}")
        st.info("To change password, ask admin (feature can be added).")

# -----------------------------
# TEACHER / ADMIN PAGES
# -----------------------------
else:
    if nav == "Home":
        st.subheader("🏠 Staff Dashboard")

        # Quick counts
        sc = run_sql("SELECT COUNT(*) AS c FROM students", fetch=True)[0]["c"]
        hc = run_sql("SELECT COUNT(*) AS c FROM homework", fetch=True)[0]["c"]
        subc = run_sql("SELECT COUNT(*) AS c FROM submissions", fetch=True)[0]["c"]
        mc = run_sql("SELECT COUNT(*) AS c FROM marks", fetch=True)[0]["c"]

        c1, c2, c3, c4 = st.columns(4)
        c1.metric("Students", sc)
        c2.metric("Homework", hc)
        c3.metric("Submissions", subc)
        c4.metric("Marks entries", mc)

    elif nav == "Homework (Create)":
        st.subheader("📝 Create Homework")
        col1, col2, col3 = st.columns([1,1,2])

        with col1:
            cls = st.selectbox("Class", CLASSES)
        with col2:
            subject = st.selectbox("Subject", SUBJECTS[cls])
        with col3:
            title = st.text_input("Title")

        desc = st.text_area("Description / Instructions")
        due = st.date_input("Due date (optional)", value=None)

        if st.button("Publish Homework", use_container_width=True):
            if not title.strip():
                st.error("Title is required.")
            elif not desc.strip():
                st.error("Description is required.")
            else:
                run_sql(
                    """INSERT INTO homework(class_name,subject,title,description,due_date,created_at)
                       VALUES(?,?,?,?,?,?)""",
                    (
                        cls,
                        subject,
                        title.strip(),
                        desc.strip(),
                        (due.isoformat() if isinstance(due, date) else None),
                        datetime.now().isoformat(timespec="seconds")
                    )
                )
                st.success("Homework published!")
                st.rerun()

        st.markdown("### 📚 Existing Homework")
        rows = run_sql("SELECT * FROM homework ORDER BY created_at DESC", fetch=True)
        if rows:
            df = pd.DataFrame([dict(r) for r in rows])[["id","class_name","subject","title","due_date","created_at"]]
            st.dataframe(df, use_container_width=True)

            del_id = st.number_input("Delete homework by ID", min_value=0, step=1)
            if st.button("Delete", use_container_width=True):
                run_sql("DELETE FROM homework WHERE id=?", (int(del_id),))
                st.success("Deleted (if ID existed).")
                st.rerun()
        else:
            st.info("No homework yet.")

    elif nav == "Submissions (View)":
        st.subheader("📥 View Homework Submissions")
        cls = st.selectbox("Class", CLASSES)
        hws = run_sql("SELECT * FROM homework WHERE class_name=? ORDER BY created_at DESC", (cls,), fetch=True)

        if not hws:
            st.info("No homework for this class.")
        else:
            hw_map = {f"#{r['id']} | {r['subject']} | {r['title']}": r["id"] for r in hws}
            hw_pick = st.selectbox("Homework", list(hw_map.keys()))
            hw_id = hw_map[hw_pick]

            rows = run_sql("""
                SELECT s.id AS sub_id, s.submitted_at, s.text_answer, s.file_name,
                       st.name AS student_name, st.class_name
                FROM submissions s
                JOIN students st ON st.id = s.student_id
                WHERE s.homework_id=?
                ORDER BY s.submitted_at DESC
            """, (hw_id,), fetch=True)

            if not rows:
                st.info("No submissions yet.")
            else:
                df = pd.DataFrame([dict(r) for r in rows])
                st.dataframe(df[["sub_id","submitted_at","student_name","file_name"]], use_container_width=True)

                pick_id = st.selectbox("Open submission", df["sub_id"].tolist())
                one = run_sql("SELECT * FROM submissions WHERE id=?", (pick_id,), fetch=True)[0]
                st.write("**Submitted at:**", one["submitted_at"])
                st.write("**Text Answer:**")
                st.code(one["text_answer"] or "")

                if one["file_bytes"] is not None:
                    st.download_button(
                        "Download attached file",
                        data=one["file_bytes"],
                        file_name=one["file_name"] or "submission.bin",
                        use_container_width=True
                    )

    elif nav == "Attendance (Take)":
        st.subheader("🗓️ Take Attendance")
        cls = st.selectbox("Class", CLASSES)
        att_date = st.date_input("Date", value=date.today())

        students = run_sql("SELECT * FROM students WHERE class_name=? ORDER BY name", (cls,), fetch=True)
        if not students:
            st.info("No students in this class.")
        else:
            # Load existing attendance for date/class
            existing = run_sql("""
                SELECT student_id, status FROM attendance
                WHERE class_name=? AND att_date=?
            """, (cls, att_date.isoformat()), fetch=True)
            existing_map = {r["student_id"]: r["status"] for r in existing}

            st.markdown("### Mark Present/Absent")
            records = []
            for r in students:
                sid = r["id"]
                default = existing_map.get(sid, "Present")
                status = st.radio(
                    f"{r['name']} (ID {sid})",
                    ["Present", "Absent"],
                    index=0 if default == "Present" else 1,
                    horizontal=True,
                    key=f"att_{sid}"
                )
                records.append((cls, att_date.isoformat(), sid, status))

            if st.button("Save Attendance", use_container_width=True):
                # Replace old for that date/class
                run_sql("DELETE FROM attendance WHERE class_name=? AND att_date=?", (cls, att_date.isoformat()))
                run_sql(
                    "INSERT INTO attendance(class_name, att_date, student_id, status) VALUES(?,?,?,?)",
                    records,
                    many=True
                )
                st.success("Attendance saved!")
                st.rerun()

    elif nav == "Marks (Enter)":
        st.subheader("🧾 Enter Marks")
        cls = st.selectbox("Class", CLASSES)
        exam = st.text_input("Exam name (e.g., Midterm, Final)")
        subject = st.selectbox("Subject", SUBJECTS[cls])
        total = st.number_input("Total marks", min_value=1.0, value=100.0)

        students = run_sql("SELECT * FROM students WHERE class_name=? ORDER BY name", (cls,), fetch=True)
        if not students:
            st.info("No students in this class.")
        else:
            st.markdown("### Enter marks")
            marks_rows = []
            for r in students:
                m = st.number_input(f"{r['name']} (ID {r['id']})", min_value=0.0, max_value=float(total), value=0.0, key=f"m_{r['id']}")
                marks_rows.append((r["id"], cls, exam.strip(), subject, float(m), float(total), datetime.now().isoformat(timespec="seconds")))

            c1, c2 = st.columns(2)
            with c1:
                if st.button("Save Marks", use_container_width=True):
                    if not exam.strip():
                        st.error("Exam name is required.")
                    else:
                        # Remove existing marks for same class+exam+subject (optional behavior)
                        run_sql("DELETE FROM marks WHERE class_name=? AND exam=? AND subject=?", (cls, exam.strip(), subject))
                        run_sql(
                            """INSERT INTO marks(student_id,class_name,exam,subject,marks,total,entered_at)
                               VALUES(?,?,?,?,?,?,?)""",
                            marks_rows,
                            many=True
                        )
                        st.success("Marks saved!")
                        st.rerun()

            with c2:
                st.markdown("#### CSV Bulk Upload (optional)")
                template = pd.DataFrame({
                    "student_id": [students[0]["id"]],
                    "class_name": [cls],
                    "exam": [exam.strip() or "Midterm"],
                    "subject": [subject],
                    "marks": [75],
                    "total": [total],
                })
                st.download_button("Download CSV template", template.to_csv(index=False), "marks_template.csv", use_container_width=True)

                up = st.file_uploader("Upload marks CSV", type=["csv"])
                if up is not None and st.button("Import CSV", use_container_width=True):
                    df = pd.read_csv(up)
                    required = {"student_id","class_name","exam","subject","marks","total"}
                    if not required.issubset(set(df.columns)):
                        st.error(f"CSV must contain columns: {sorted(required)}")
                    else:
                        rows = []
                        now = datetime.now().isoformat(timespec="seconds")
                        for _, rr in df.iterrows():
                            rows.append((
                                int(rr["student_id"]),
                                str(rr["class_name"]),
                                str(rr["exam"]),
                                str(rr["subject"]),
                                float(rr["marks"]),
                                float(rr["total"]),
                                now
                            ))
                        run_sql(
                            """INSERT INTO marks(student_id,class_name,exam,subject,marks,total,entered_at)
                               VALUES(?,?,?,?,?,?,?)""",
                            rows,
                            many=True
                        )
                        st.success("Imported marks!")
                        st.rerun()

    elif nav == "Results (View)":
        st.subheader("📊 View Results (Marksheet)")
        cls = st.selectbox("Class", CLASSES)

        exams = run_sql("SELECT DISTINCT exam FROM marks WHERE class_name=? ORDER BY exam", (cls,), fetch=True)
        if not exams:
            st.info("No marks for this class yet.")
        else:
            exam = st.selectbox("Exam", [r["exam"] for r in exams])

            rows = run_sql("""
                SELECT st.id AS student_id, st.name, m.subject, m.marks, m.total
                FROM marks m
                JOIN students st ON st.id = m.student_id
                WHERE m.class_name=? AND m.exam=?
                ORDER BY st.name, m.subject
            """, (cls, exam), fetch=True)

            if not rows:
                st.info("No rows found.")
            else:
                df = pd.DataFrame([dict(r) for r in rows])
                # Pivot to marksheet
                pivot = df.pivot_table(index=["student_id","name"], columns="subject", values="marks", aggfunc="first")
                pivot["Total"] = pivot.sum(axis=1, numeric_only=True)
                st.dataframe(pivot.reset_index(), use_container_width=True)

    elif nav == "Students/Users":
        st.subheader("👥 Students & User Accounts")

        # Students list
        cls = st.selectbox("Class (filter)", ["All"] + CLASSES)
        if cls == "All":
            students = run_sql("SELECT * FROM students ORDER BY class_name, name", fetch=True)
        else:
            students = run_sql("SELECT * FROM students WHERE class_name=? ORDER BY name", (cls,), fetch=True)

        if students:
            sdf = pd.DataFrame([dict(r) for r in students])
            st.dataframe(sdf, use_container_width=True)
        else:
            st.info("No students found.")

        if role == "admin":
            st.markdown("### ➕ Add Student + Create Login")
            c1, c2 = st.columns(2)
            with c1:
                new_name = st.text_input("Student name")
            with c2:
                new_cls = st.selectbox("Class", CLASSES, key="new_cls")

            if st.button("Add Student", use_container_width=True):
                if not new_name.strip():
                    st.error("Name required.")
                else:
                    run_sql("INSERT INTO students(name,class_name) VALUES(?,?)", (new_name.strip(), new_cls))
                    new_sid = run_sql("SELECT id FROM students ORDER BY id DESC LIMIT 1", fetch=True)[0]["id"]

                    username = f"s{1000 + new_sid}"
                    password = "student123"
                    salt = make_salt()
                    run_sql(
                        "INSERT OR REPLACE INTO users(username,salt,pw_hash,role,student_id) VALUES(?,?,?,?,?)",
                        (username, salt, hash_pw(password, salt), "student", new_sid)
                    )

                    st.success(f"Added! Student login: {username} / {password}")
                    st.rerun()

            st.markdown("### 🔑 Reset Password (Admin)")
            uname = st.text_input("Username to reset")
            newpw = st.text_input("New password", type="password")
            if st.button("Reset Password", use_container_width=True):
                u = get_user(uname.strip())
                if not u:
                    st.error("User not found.")
                elif not newpw:
                    st.error("New password required.")
                else:
                    salt = make_salt()
                    run_sql(
                        "UPDATE users SET salt=?, pw_hash=? WHERE username=?",
                        (salt, hash_pw(newpw, salt), uname.strip())
                    )
                    st.success("Password reset done.")

        else:
            st.info("Only Admin can add students / reset passwords.")