from flask import (Flask, render_template, request, redirect,
                   url_for, session, jsonify, flash, Response)
from werkzeug.security import generate_password_hash, check_password_hash
from functools import wraps
import csv, io, os
import database as db
from data import ALL_PROGRAMS, EXERCISES, MODULES
from quiz_data import (QUIZZES, MODULE_SLUGS, get_quiz,
                       get_module_from_slug, get_slug_from_module)
from certificate import generate_certificate
from mailer import (send_welcome, send_comment_notification,
                    send_password_reset, send_announcement,
                    send_assignment_notification)
from gamification import (BADGES, LEVELS, XP_RULES, get_level_info,
                           check_badges)

app = Flask(__name__)
app.secret_key = os.environ.get("SECRET_KEY", "python108-dev-secret-key-change-in-production")
DB_PATH  = "/home/viswamin/python.viswaminfotech/python.viswaminfotech.com/python108.db",
# ── Custom Jinja2 filters & globals ─────────────────────────────
app.jinja_env.filters['enumerate'] = enumerate

@app.context_processor
def inject_globals():
    from datetime import date
    today = date.today()
    return {
        "now_date":     today.isoformat(),
        "now_date_obj": today,
    }


def award_xp_and_badges(user_id, action, xp, note=""):
    """Award XP for an action and check for newly unlocked badges."""
    # Prevent double-awarding idempotent actions
    if note and db.has_earned_xp_for(user_id, action, note):
        return 0, []
    db.award_xp(user_id, action, xp, note)
    new_badges = check_badges(user_id, db, ALL_PROGRAMS, MODULES)
    return xp, new_badges


db.init_db()

# ── Decorators ───────────────────────────────────────────────────
def login_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        if "user_id" not in session:
            return redirect(url_for("login"))
        return f(*args, **kwargs)
    return decorated

def teacher_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        if "user_id" not in session:
            return redirect(url_for("login"))
        if session.get("role") not in ("teacher", "admin"):
            flash("Teacher access required.", "error")
            return redirect(url_for("dashboard"))
        return f(*args, **kwargs)
    return decorated

def admin_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        if "user_id" not in session:
            return redirect(url_for("login"))
        if session.get("role") != "admin":
            flash("Admin access required.", "error")
            return redirect(url_for("dashboard"))
        return f(*args, **kwargs)
    return decorated

def current_user():
    if "user_id" in session:
        return db.get_user_by_id(session["user_id"])
    return None

# ── Auth ─────────────────────────────────────────────────────────
@app.route("/")
def index():
    if "user_id" in session:
        role = session.get("role", "student")
        if role == "admin":    return redirect(url_for("admin_dashboard"))
        if role == "teacher":  return redirect(url_for("teacher_dashboard"))
        return redirect(url_for("dashboard"))
    return render_template("landing.html")

@app.route("/register", methods=["GET","POST"])
def register():
    if request.method == "POST":
        username = request.form["username"].strip()
        email    = request.form["email"].strip()
        password = request.form["password"]
        if len(username) < 3:
            flash("Username must be at least 3 characters.", "error")
        elif len(password) < 6:
            flash("Password must be at least 6 characters.", "error")
        else:
            pw_hash = generate_password_hash(password)
            if db.create_user(username, email, pw_hash, role="student"):
                send_welcome(username, email)
                flash("Account created! Please log in.", "success")
                return redirect(url_for("login"))
            else:
                flash("Username or email already taken.", "error")
    return render_template("register.html")

@app.route("/login", methods=["GET","POST"])
def login():
    if request.method == "POST":
        username = request.form["username"].strip()
        password = request.form["password"]
        user = db.get_user_by_username(username)
        if user and check_password_hash(user["password_hash"], password):
            if not user["is_active"]:
                flash("Your account has been deactivated. Contact admin.", "error")
                return render_template("login.html")
            session["user_id"]  = user["id"]
            session["username"] = user["username"]
            session["role"]     = user["role"]
            role = user["role"]
            if role == "admin":   return redirect(url_for("admin_dashboard"))
            if role == "teacher": return redirect(url_for("teacher_dashboard"))
            return redirect(url_for("dashboard"))
        flash("Invalid username or password.", "error")
    return render_template("login.html")

@app.route("/logout")
def logout():
    session.clear()
    return redirect(url_for("login"))

# ── Student routes ───────────────────────────────────────────────
@app.route("/dashboard")
@login_required
def dashboard():
    user = current_user()
    if user["role"] == "admin":   return redirect(url_for("admin_dashboard"))
    if user["role"] == "teacher": return redirect(url_for("teacher_dashboard"))

    prog_progress = db.get_program_progress(user["id"])
    ex_progress   = db.get_exercise_progress(user["id"])
    stats = db.get_stats(user["id"])

    # Fetch bookmarks early — needed inside the modules loop below
    bookmarks_set = db.get_bookmarks(user["id"])
    notes_map     = db.get_all_notes(user["id"])

    modules = {}
    for p in ALL_PROGRAMS:
        m = p["mod"]
        if m not in modules:
            modules[m] = []
        modules[m].append({
            **p,
            "done":       prog_progress.get(p["n"], 0) == 1,
            "bookmarked": p["n"] in bookmarks_set,
        })

    next_prog = next((p for p in ALL_PROGRAMS if prog_progress.get(p["n"], 0) == 0), None)
    pct = round(stats["programs_done"] / 108 * 100)

    # Class & leaderboard
    student_class = db.get_class_for_student(user["id"])
    leaderboard   = []
    assignments   = []
    announcements = []
    if student_class:
        leaderboard   = db.get_class_leaderboard(student_class["id"], limit=5)
        assignments   = db.get_assignments_for_student(user["id"])
        announcements = db.get_announcements_for_student(user["id"])

    # Streak & activity
    streak      = db.get_streak_data(user["id"])
    activity    = db.get_activity_last_n_days(user["id"], n=30)
    session["streak"] = streak["current_streak"]   # for navbar

    # Gamification
    total_xp   = db.get_total_xp(user["id"])
    level_info = get_level_info(total_xp)
    session["level_icon"] = level_info["icon"]

    # Quiz results
    quiz_results = db.get_all_quiz_results(user["id"])
    quizzes_passed = sum(1 for r in quiz_results.values() if r["passed"])

    # Class leaderboard — XP-based
    show_xp = True
    if student_class:
        show_xp = bool(db.get_leaderboard_setting(student_class["id"]))
        leaderboard = db.get_class_xp_leaderboard(student_class["id"], limit=5)

    return render_template("dashboard.html",
        user=user, modules=modules, stats=stats,
        next_prog=next_prog, pct=pct,
        ex_progress=ex_progress,
        student_class=student_class,
        leaderboard=leaderboard,
        streak=streak, activity=activity,
        quiz_results=quiz_results,
        quizzes_passed=quizzes_passed,
        module_slugs=MODULE_SLUGS,
        bookmarks_count=len(bookmarks_set),
        notes_count=len(notes_map),
        assignments=assignments,
        announcements=announcements,
        level_info=level_info,
        total_xp=total_xp,
        show_xp=show_xp)

@app.route("/program/<int:n>")
@login_required
def program(n):
    user = current_user()
    prog = next((p for p in ALL_PROGRAMS if p["n"] == n), None)
    if not prog:
        return redirect(url_for("dashboard"))
    prog_progress = db.get_program_progress(user["id"])
    is_done       = prog_progress.get(n, 0) == 1
    exercise      = next((e for e in EXERCISES if e["n"] == n), None)
    streak        = db.get_streak_data(user["id"])
    note          = db.get_note(user["id"], n)
    bookmarks     = db.get_bookmarks(user["id"])
    is_bookmarked = n in bookmarks
    # Teacher comments and assignment deadline
    prog_comments = db.get_comments_for_program(user["id"], n)
    all_assigns   = db.get_assignments_for_student(user["id"])
    deadline      = next((a for a in all_assigns if a["program_n"] == n), None)
    return render_template("program.html",
        user=user, prog=prog, is_done=is_done,
        prev_n=n-1 if n>1 else None,
        next_n=n+1 if n<108 else None,
        exercise=exercise, streak=streak,
        note=note, is_bookmarked=is_bookmarked,
        prog_comments=prog_comments,
        deadline=deadline)

@app.route("/toggle_program/<int:n>", methods=["POST"])
@login_required
def toggle_program(n):
    user_id = session["user_id"]
    db.toggle_program_complete(user_id, n)
    prog_progress = db.get_program_progress(user_id)
    xp_earned  = 0
    new_badges = []
    if prog_progress.get(n, 0) == 1:
        db.record_activity(user_id)
        # Award XP for program completion
        xp_earned, new_badges = award_xp_and_badges(
            user_id, "program_complete", XP_RULES["program_complete"],
            note=str(n)
        )
        # Daily bonus — first program of the day
        from datetime import date
        today = date.today().isoformat()
        if not db.has_earned_xp_for(user_id, "daily_bonus", today):
            db.award_xp(user_id, "daily_bonus", XP_RULES["daily_bonus"], today)
            xp_earned += XP_RULES["daily_bonus"]
        # Check module completion bonus
        prog = next((p for p in ALL_PROGRAMS if p["n"] == n), None)
        if prog:
            mod_progs = [p["n"] for p in ALL_PROGRAMS if p["mod"] == prog["mod"]]
            if all(prog_progress.get(pn, 0) == 1 for pn in mod_progs):
                bonus_key = f"module_complete_{prog['mod']}"
                if not db.has_earned_xp_for(user_id, "module_complete", bonus_key):
                    db.award_xp(user_id, "module_complete",
                                XP_RULES["module_complete"], bonus_key)
                    xp_earned += XP_RULES["module_complete"]
                    new_badges += check_badges(user_id, db, ALL_PROGRAMS, MODULES)
        # All 108 bonus
        if sum(1 for v in prog_progress.values() if v == 1) >= 108:
            if not db.has_earned_xp_for(user_id, "all_programs", "done"):
                db.award_xp(user_id, "all_programs", XP_RULES["all_programs"], "done")
                xp_earned += XP_RULES["all_programs"]

    streak     = db.get_streak_data(user_id)
    total_xp   = db.get_total_xp(user_id)
    level_info = get_level_info(total_xp)

    # Deduplicate new_badges
    new_badges = list(dict.fromkeys(new_badges))
    new_badge_data = [
        {"key": k, "name": BADGES[k]["name"], "icon": BADGES[k]["icon"]}
        for k in new_badges if k in BADGES
    ]
    return jsonify({
        "ok": True,
        "streak":      streak["current_streak"],
        "studied_today": streak["studied_today"],
        "xp_earned":   xp_earned,
        "total_xp":    total_xp,
        "level":       level_info["level"],
        "level_name":  level_info["name"],
        "level_icon":  level_info["icon"],
        "new_badges":  new_badge_data,
    })

@app.route("/exercises")
@login_required
def exercises():
    user = current_user()
    ex_progress  = db.get_exercise_progress(user["id"])
    diff_filter  = request.args.get("diff","")
    mod_filter   = request.args.get("mod","")
    exs = EXERCISES
    if diff_filter:
        exs = [e for e in exs if e["diff"] == diff_filter]
    if mod_filter:
        prog_ns = {p["n"] for p in ALL_PROGRAMS if p["mod"] == mod_filter}
        exs = [e for e in exs if e["n"] in prog_ns]
    solved = sum(1 for e in EXERCISES if ex_progress.get(e["n"], 0) == 1)
    return render_template("exercises.html",
        user=user, exercises=exs, ex_progress=ex_progress,
        diff_filter=diff_filter, mod_filter=mod_filter,
        modules=MODULES, solved=solved, total=len(EXERCISES))

@app.route("/exercise/<int:n>")
@login_required
def exercise(n):
    user = current_user()
    ex   = next((e for e in EXERCISES if e["n"] == n), None)
    if not ex:
        return redirect(url_for("exercises"))
    prog = next((p for p in ALL_PROGRAMS if p["n"] == n), None)
    ex_progress = db.get_exercise_progress(user["id"])
    return render_template("exercise.html",
        user=user, ex=ex, prog=prog,
        is_solved=ex_progress.get(n, 0) == 1)

@app.route("/toggle_exercise/<int:n>", methods=["POST"])
@login_required
def toggle_exercise(n):
    user_id = session["user_id"]
    db.toggle_exercise_solved(user_id, n)
    ex_progress = db.get_exercise_progress(user_id)
    xp_earned  = 0
    new_badges = []
    if ex_progress.get(n, 0) == 1:
        xp_earned, new_badges = award_xp_and_badges(
            user_id, "exercise_solved", XP_RULES["exercise_solved"], note=str(n)
        )
    total_xp   = db.get_total_xp(user_id)
    level_info = get_level_info(total_xp)
    new_badge_data = [
        {"key": k, "name": BADGES[k]["name"], "icon": BADGES[k]["icon"]}
        for k in new_badges if k in BADGES
    ]
    return jsonify({
        "ok": True,
        "xp_earned":  xp_earned,
        "total_xp":   total_xp,
        "level":      level_info["level"],
        "new_badges": new_badge_data,
    })

# ── Teacher routes ───────────────────────────────────────────────
@app.route("/teacher")
@teacher_required
def teacher_dashboard():
    user    = current_user()
    classes = db.get_classes_for_teacher(user["id"])
    return render_template("teacher/dashboard.html", user=user, classes=classes)

@app.route("/teacher/class/<int:class_id>")
@teacher_required
def teacher_class(class_id):
    user     = current_user()
    cls      = db.get_class_by_id(class_id)
    if not cls:
        flash("Class not found.", "error")
        return redirect(url_for("teacher_dashboard"))
    # Security: teachers can only see their own classes (admin can see all)
    if user["role"] == "teacher" and cls["teacher_id"] != user["id"]:
        flash("You do not have access to this class.", "error")
        return redirect(url_for("teacher_dashboard"))

    students     = db.get_students_in_class(class_id)
    summary      = db.get_student_summary_for_class(class_id)
    matrix       = db.get_class_progress_matrix(class_id)
    streaks      = db.get_class_streaks(class_id)
    quiz_summary = db.get_class_quiz_summary(class_id)
    assignments  = db.get_assignments_for_class(class_id)
    announcements = db.get_announcements_for_class(class_id, active_only=False)

    # Build heatmap data: list of {student, modules: [{mod, cells:[0/1]}]}
    mod_groups = {}
    for p in ALL_PROGRAMS:
        mod_groups.setdefault(p["mod"], []).append(p["n"])

    heatmap = []
    for s in students:
        done_set = matrix.get(s["id"], set())
        mods = []
        for mod_name, prog_ns in mod_groups.items():
            mods.append({
                "mod": mod_name,
                "cells": [1 if n in done_set else 0 for n in prog_ns]
            })
        heatmap.append({"student": s, "mods": mods,
                        "total": len(done_set)})

    return render_template("teacher/class.html",
        user=user, cls=cls, students=students,
        summary=summary, heatmap=heatmap,
        mod_names=list(mod_groups.keys()),
        all_programs=ALL_PROGRAMS,
        streaks=streaks,
        quiz_summary=quiz_summary,
        assignments=assignments,
        announcements=announcements,
        leaderboard_show_xp=db.get_leaderboard_setting(class_id))

@app.route("/teacher/student/<int:student_id>")
@teacher_required
def teacher_student(student_id):
    user    = current_user()
    student = db.get_user_by_id(student_id)
    if not student:
        flash("Student not found.", "error")
        return redirect(url_for("teacher_dashboard"))

    prog_progress = db.get_program_progress(student_id)
    ex_progress   = db.get_exercise_progress(student_id)
    stats         = db.get_stats(student_id)
    pct           = round(stats["programs_done"] / 108 * 100)
    quiz_results  = db.get_all_quiz_results(student_id)
    quizzes_passed = sum(1 for r in quiz_results.values() if r["passed"])

    # Module breakdown
    mod_stats = []
    for mod_name in MODULES:
        prog_ns = [p["n"] for p in ALL_PROGRAMS if p["mod"] == mod_name]
        done    = sum(1 for n in prog_ns if prog_progress.get(n, 0) == 1)
        mod_stats.append({"mod": mod_name, "done": done, "total": len(prog_ns)})

    # Weak spots
    weak_spots = db.get_module_weak_spots(student_id, ALL_PROGRAMS, MODULES)

    # Teacher comments on this student's programs
    comments_raw = db.get_comments_for_student(student_id)
    comments_map = {c["program_n"]: c for c in comments_raw}

    # My comment form data (pre-fill if I already commented)
    my_comments = {}
    for program_n, c in comments_map.items():
        if c["teacher_id"] == user["id"]:
            my_comments[program_n] = c

    return render_template("teacher/student.html",
        user=user, student=student, stats=stats, pct=pct,
        prog_progress=prog_progress, ex_progress=ex_progress,
        mod_stats=mod_stats, all_programs=ALL_PROGRAMS,
        quiz_results=quiz_results, quizzes_passed=quizzes_passed,
        module_slugs=MODULE_SLUGS,
        weak_spots=weak_spots,
        comments_map=comments_map,
        my_comments=my_comments)

@app.route("/teacher/export/<int:class_id>")
@teacher_required
def teacher_export(class_id):
    user = current_user()
    cls  = db.get_class_by_id(class_id)
    if not cls:
        return redirect(url_for("teacher_dashboard"))
    if user["role"] == "teacher" and cls["teacher_id"] != user["id"]:
        flash("Access denied.", "error")
        return redirect(url_for("teacher_dashboard"))

    data = db.export_class_csv(class_id)
    output = io.StringIO()
    writer = csv.DictWriter(output,
        fieldnames=["username","email","prog_done","ex_done","pct","created_at"])
    writer.writeheader()
    writer.writerows(data)
    csv_content = output.getvalue()
    return Response(
        csv_content,
        mimetype="text/csv",
        headers={"Content-Disposition":
                 f"attachment; filename={cls['name'].replace(' ','_')}_report.csv"}
    )

# ── Admin routes ─────────────────────────────────────────────────
@app.route("/admin")
@admin_required
def admin_dashboard():
    user  = current_user()
    stats = db.get_site_stats()
    top   = db.get_top_students(limit=10)
    classes  = db.get_all_classes()
    teachers = db.get_all_users(role="teacher")
    return render_template("admin/dashboard.html",
        user=user, stats=stats, top_students=top,
        classes=classes, teachers=teachers)

@app.route("/admin/teachers")
@admin_required
def admin_teachers():
    user     = current_user()
    teachers = db.get_all_users(role="teacher")
    return render_template("admin/teachers.html", user=user, teachers=teachers)

@app.route("/admin/teachers/create", methods=["GET","POST"])
@admin_required
def admin_create_teacher():
    user = current_user()
    if request.method == "POST":
        username = request.form["username"].strip()
        email    = request.form["email"].strip()
        password = request.form["password"]
        if len(username) < 3 or len(password) < 6:
            flash("Username ≥ 3 chars, password ≥ 6 chars.", "error")
        else:
            pw_hash = generate_password_hash(password)
            if db.create_user(username, email, pw_hash, role="teacher"):
                flash(f"Teacher '{username}' created successfully.", "success")
                return redirect(url_for("admin_teachers"))
            else:
                flash("Username or email already taken.", "error")
    return render_template("admin/create_teacher.html", user=user)

@app.route("/admin/teachers/toggle/<int:teacher_id>", methods=["POST"])
@admin_required
def admin_toggle_teacher(teacher_id):
    t = db.get_user_by_id(teacher_id)
    if t and t["role"] == "teacher":
        db.set_user_active(teacher_id, 0 if t["is_active"] else 1)
        flash(f"Teacher {'deactivated' if t['is_active'] else 'activated'}.", "success")
    return redirect(url_for("admin_teachers"))

@app.route("/admin/classes")
@admin_required
def admin_classes():
    user    = current_user()
    classes = db.get_all_classes()
    teachers = db.get_all_users(role="teacher")
    return render_template("admin/classes.html",
        user=user, classes=classes, teachers=teachers)

@app.route("/admin/classes/create", methods=["GET","POST"])
@admin_required
def admin_create_class():
    user     = current_user()
    teachers = db.get_all_users(role="teacher")
    if request.method == "POST":
        name     = request.form["name"].strip()
        desc     = request.form.get("description","").strip()
        tid      = request.form.get("teacher_id") or None
        if not name:
            flash("Class name is required.", "error")
        else:
            db.create_class(name, desc, tid)
            flash(f"Class '{name}' created.", "success")
            return redirect(url_for("admin_classes"))
    return render_template("admin/create_class.html", user=user, teachers=teachers)

@app.route("/admin/classes/<int:class_id>/assign", methods=["POST"])
@admin_required
def admin_assign_teacher(class_id):
    teacher_id = request.form.get("teacher_id") or None
    db.assign_teacher_to_class(class_id, teacher_id)
    flash("Teacher assigned.", "success")
    return redirect(url_for("admin_classes"))

@app.route("/admin/classes/<int:class_id>/toggle", methods=["POST"])
@admin_required
def admin_toggle_class(class_id):
    cls = db.get_class_by_id(class_id)
    if cls:
        db.set_class_active(class_id, 0 if cls["is_active"] else 1)
    return redirect(url_for("admin_classes"))

@app.route("/admin/students")
@admin_required
def admin_students():
    user     = current_user()
    students = db.get_all_users(role="student")
    classes  = db.get_all_classes()
    unassigned = db.get_unassigned_students()
    return render_template("admin/students.html",
        user=user, students=students,
        classes=classes, unassigned=unassigned)

@app.route("/admin/students/assign", methods=["POST"])
@admin_required
def admin_assign_student():
    student_id = int(request.form["student_id"])
    class_id   = int(request.form["class_id"])
    db.assign_student_to_class(student_id, class_id)
    flash("Student assigned to class.", "success")
    return redirect(url_for("admin_students"))

@app.route("/admin/students/toggle/<int:student_id>", methods=["POST"])
@admin_required
def admin_toggle_student(student_id):
    s = db.get_user_by_id(student_id)
    if s and s["role"] == "student":
        db.set_user_active(student_id, 0 if s["is_active"] else 1)
    return redirect(url_for("admin_students"))

@app.route("/search")
@login_required
def search():
    user  = current_user()
    query = request.args.get("q", "").strip()
    results = []
    if query and len(query) >= 2:
        ql = query.lower()
        for p in ALL_PROGRAMS:
            score = 0
            if ql in p["title"].lower():       score += 10
            if ql in p["mod"].lower():          score += 5
            if ql in p["what"].lower():         score += 4
            if ql in p["code"].lower():         score += 3
            if any(ql in l.lower() for l in p["learns"]): score += 3
            for e in EXERCISES:
                if e["n"] == p["n"] and ql in e["q"].lower(): score += 2
            if score:
                results.append({"program": p, "score": score})
        results.sort(key=lambda x: -x["score"])
        results = results[:30]
    return render_template("search.html",
        user=user, query=query, results=results)


@app.route("/bookmarks")
@login_required
def bookmarks():
    user    = current_user()
    bm_set  = db.get_bookmarks(user["id"])
    prog_progress = db.get_program_progress(user["id"])
    bookmarked = [
        {**p, "done": prog_progress.get(p["n"], 0) == 1}
        for p in ALL_PROGRAMS if p["n"] in bm_set
    ]
    notes = db.get_all_notes(user["id"])
    return render_template("bookmarks.html",
        user=user, bookmarked=bookmarked, notes=notes,
        all_programs=ALL_PROGRAMS)


@app.route("/toggle_bookmark/<int:n>", methods=["POST"])
@login_required
def toggle_bookmark(n):
    is_bookmarked = db.toggle_bookmark(session["user_id"], n)
    return jsonify({"ok": True, "bookmarked": is_bookmarked})


@app.route("/save_note/<int:n>", methods=["POST"])
@login_required
def save_note(n):
    note = request.json.get("note", "")
    db.save_note(session["user_id"], n, note)
    return jsonify({"ok": True})


@app.route("/teacher/class/<int:class_id>/assignments")
@teacher_required
def teacher_assignments(class_id):
    user = current_user()
    cls  = db.get_class_by_id(class_id)
    if not cls or (user["role"] == "teacher" and cls["teacher_id"] != user["id"]):
        flash("Access denied.", "error")
        return redirect(url_for("teacher_dashboard"))
    assignments, status = db.get_assignment_status_for_class(class_id)
    students = db.get_students_in_class(class_id)
    prog_map = {p["n"]: p for p in ALL_PROGRAMS}
    return render_template("teacher/assignments.html",
        user=user, cls=cls,
        assignments=assignments, status=status,
        students=students, prog_map=prog_map)


@app.route("/teacher/class/<int:class_id>/assign", methods=["POST"])
@teacher_required
def teacher_create_assignment(class_id):
    user = current_user()
    cls  = db.get_class_by_id(class_id)
    if not cls or (user["role"] == "teacher" and cls["teacher_id"] != user["id"]):
        flash("Access denied.", "error")
        return redirect(url_for("teacher_dashboard"))
    program_n = request.form.get("program_n", type=int)
    due_date  = request.form.get("due_date", "")
    note      = request.form.get("note", "").strip()
    if not program_n or not due_date:
        flash("Program and due date are required.", "error")
    else:
        db.create_assignment(class_id, user["id"], program_n, due_date, note)
        prog = next((p for p in ALL_PROGRAMS if p["n"] == program_n), None)
        # Email all students in the class
        students = db.get_students_in_class(class_id)
        for s in students:
            send_assignment_notification(
                s["username"], s["email"],
                program_n, prog["title"] if prog else f"Program {program_n}",
                due_date, note, cls["name"]
            )
        flash(f"Assignment created: #{program_n} {prog['title'] if prog else ''} due {due_date}", "success")
    return redirect(url_for("teacher_assignments", class_id=class_id))


@app.route("/teacher/assignment/<int:assignment_id>/delete", methods=["POST"])
@teacher_required
def teacher_delete_assignment(assignment_id):
    user    = current_user()
    # Find the class_id for redirect
    conn    = db.get_db()
    a       = conn.execute("SELECT * FROM assignments WHERE id=?", (assignment_id,)).fetchone()
    conn.close()
    if a:
        db.delete_assignment(assignment_id, user["id"])
        flash("Assignment removed.", "success")
        return redirect(url_for("teacher_assignments", class_id=a["class_id"]))
    return redirect(url_for("teacher_dashboard"))


@app.route("/teacher/comment/<int:student_id>/<int:program_n>", methods=["POST"])
@teacher_required
def teacher_save_comment(student_id, program_n):
    user    = current_user()
    comment = request.form.get("comment", "").strip()
    rating  = request.form.get("rating", 0, type=int)
    rating  = max(0, min(5, rating))
    db.save_comment(user["id"], student_id, program_n, comment, rating)
    # Send email notification to student
    student = db.get_user_by_id(student_id)
    prog    = next((p for p in ALL_PROGRAMS if p["n"] == program_n), None)
    if student and comment:
        send_comment_notification(
            student["username"], student["email"],
            user["username"], program_n,
            prog["title"] if prog else f"Program {program_n}",
            comment, rating
        )
    flash("Comment saved.", "success")
    return redirect(url_for("teacher_student", student_id=student_id) +
                    f"#comment-{program_n}")


@app.route("/teacher/comment/<int:student_id>/<int:program_n>/delete", methods=["POST"])
@teacher_required
def teacher_delete_comment(student_id, program_n):
    user = current_user()
    db.delete_comment(user["id"], student_id, program_n)
    flash("Comment deleted.", "success")
    return redirect(url_for("teacher_student", student_id=student_id))


# ── Certificate routes ───────────────────────────────────────────
@app.route("/certificate/<username>")
@login_required
def certificate(username):
    user    = current_user()
    subject = db.get_user_by_username(username)
    if not subject:
        flash("Student not found.", "error")
        return redirect(url_for("dashboard"))
    # Students can only download their own certificate
    if user["role"] == "student" and user["username"] != username:
        flash("You can only download your own certificate.", "error")
        return redirect(url_for("dashboard"))
    if not db.is_course_complete(subject["id"]):
        flash(f"{username} has not completed all 108 programs yet.", "error")
        return redirect(url_for("dashboard"))
    completion_date = db.get_completion_date(subject["id"])
    pdf = generate_certificate(username, completion_date)
    return Response(
        pdf,
        mimetype="application/pdf",
        headers={
            "Content-Disposition":
                f'attachment; filename="Python108_Certificate_{username}.pdf"'
        }
    )


@app.route("/certificate_preview")
@login_required
def certificate_preview():
    user = current_user()
    if user["role"] != "student":
        return redirect(url_for("dashboard"))
    is_complete     = db.is_course_complete(user["id"])
    completion_date = db.get_completion_date(user["id"]) if is_complete else None
    stats           = db.get_stats(user["id"])
    return render_template("certificate_preview.html",
        user=user, is_complete=is_complete,
        completion_date=completion_date, stats=stats)


# ── Admin bulk import routes ─────────────────────────────────────
@app.route("/admin/import", methods=["GET", "POST"])
@admin_required
def admin_import():
    user    = current_user()
    results = None
    summary = None
    classes = db.get_all_classes()

    if request.method == "POST":
        file = request.files.get("csv_file")
        if not file or not file.filename.endswith(".csv"):
            flash("Please upload a valid .csv file.", "error")
            return render_template("admin/import.html",
                user=user, classes=classes, results=None, summary=None)

        try:
            stream  = io.StringIO(file.stream.read().decode("utf-8-sig"))
            reader  = csv.DictReader(stream)
            # Normalise headers — lowercase and strip spaces
            rows = []
            for row in reader:
                rows.append({k.strip().lower(): v for k, v in row.items()})

            if not rows:
                flash("CSV file is empty.", "error")
                return render_template("admin/import.html",
                    user=user, classes=classes, results=None, summary=None)

            results = db.bulk_import_students(
                rows,
                password_hasher=lambda p: __import__(
                    'werkzeug.security', fromlist=['generate_password_hash']
                ).generate_password_hash(p)
            )
            created  = sum(1 for r in results if r["status"] == "created")
            skipped  = sum(1 for r in results if r["status"] == "skipped")
            failed   = sum(1 for r in results if r["status"] == "failed")
            summary  = {"created": created, "skipped": skipped, "failed": failed,
                        "total": len(results)}
            flash(f"Import complete: {created} created, {skipped} skipped, {failed} failed.",
                  "success" if failed == 0 else "error")
        except Exception as e:
            flash(f"Error reading CSV: {e}", "error")

    return render_template("admin/import.html",
        user=user, classes=classes, results=results, summary=summary)


@app.route("/admin/import/template")
@admin_required
def admin_import_template():
    """Download a sample CSV template."""
    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow(["username", "email", "password", "class_name"])
    writer.writerow(["student1", "student1@school.com", "pass123", "Batch A"])
    writer.writerow(["student2", "student2@school.com", "pass123", "Batch A"])
    writer.writerow(["student3", "student3@school.com", "pass456", "Batch B"])
    return Response(
        output.getvalue(),
        mimetype="text/csv",
        headers={"Content-Disposition": "attachment; filename=student_import_template.csv"}
    )


# ── Announcement routes ──────────────────────────────────────────
@app.route("/teacher/class/<int:class_id>/announcements")
@teacher_required
def teacher_announcements(class_id):
    user = current_user()
    cls  = db.get_class_by_id(class_id)
    if not cls or (user["role"] == "teacher" and cls["teacher_id"] != user["id"]):
        flash("Access denied.", "error")
        return redirect(url_for("teacher_dashboard"))
    announcements = db.get_announcements_for_class(class_id, active_only=False)
    return render_template("teacher/announcements.html",
        user=user, cls=cls, announcements=announcements)


@app.route("/teacher/class/<int:class_id>/announce", methods=["POST"])
@teacher_required
def teacher_create_announcement(class_id):
    user = current_user()
    cls  = db.get_class_by_id(class_id)
    if not cls or (user["role"] == "teacher" and cls["teacher_id"] != user["id"]):
        flash("Access denied.", "error")
        return redirect(url_for("teacher_dashboard"))
    title      = request.form.get("title", "").strip()
    body       = request.form.get("body", "").strip()
    pinned     = 1 if request.form.get("pinned") else 0
    expires_at = request.form.get("expires_at") or None
    if not title:
        flash("Title is required.", "error")
    else:
        db.create_announcement(class_id, user["id"], title, body, pinned, expires_at)
        # Email all students in class
        students = db.get_students_in_class(class_id)
        for s in students:
            send_announcement(s["username"], s["email"],
                              cls["name"], title, body, user["username"])
        flash(f"Announcement posted to {len(students)} student(s).", "success")
    return redirect(url_for("teacher_announcements", class_id=class_id))


@app.route("/teacher/announcement/<int:ann_id>/delete", methods=["POST"])
@teacher_required
def teacher_delete_announcement(ann_id):
    conn = db.get_db()
    a    = conn.execute("SELECT * FROM announcements WHERE id=?", (ann_id,)).fetchone()
    conn.close()
    if a:
        db.delete_announcement(ann_id)
        flash("Announcement deleted.", "success")
        return redirect(url_for("teacher_announcements", class_id=a["class_id"]))
    return redirect(url_for("teacher_dashboard"))


@app.route("/admin/announcements")
@admin_required
def admin_announcements():
    user          = current_user()
    announcements = db.get_all_announcements()
    return render_template("admin/announcements.html",
        user=user, announcements=announcements)


@app.route("/admin/announcement/<int:ann_id>/delete", methods=["POST"])
@admin_required
def admin_delete_announcement(ann_id):
    db.delete_announcement(ann_id)
    flash("Announcement deleted.", "success")
    return redirect(url_for("admin_announcements"))


# ── Password reset routes ────────────────────────────────────────
@app.route("/forgot-password", methods=["GET", "POST"])
def forgot_password():
    if request.method == "POST":
        email    = request.form.get("email", "").strip()
        username = request.form.get("username", "").strip()
        user     = db.get_user_by_username(username)
        if user and user["email"] == email:
            token = db.create_password_reset_token(user["id"])
            send_password_reset(user["username"], user["email"], token)
            flash("Password reset link has been sent (check your terminal in console mode).", "success")
            return redirect(url_for("login"))
        else:
            # Vague message to prevent user enumeration
            flash("If that account exists, a reset link has been sent.", "success")
            return redirect(url_for("login"))
    return render_template("reset_request.html")


@app.route("/reset-password/<token>", methods=["GET", "POST"])
def reset_password(token):
    record = db.get_valid_reset_token(token)
    if not record:
        flash("This reset link is invalid or has expired. Please request a new one.", "error")
        return redirect(url_for("forgot_password"))
    if request.method == "POST":
        password = request.form.get("password", "")
        confirm  = request.form.get("confirm", "")
        if len(password) < 6:
            flash("Password must be at least 6 characters.", "error")
        elif password != confirm:
            flash("Passwords do not match.", "error")
        else:
            pw_hash = generate_password_hash(password)
            db.use_reset_token(token, pw_hash)
            flash("Password reset successfully. Please log in.", "success")
            return redirect(url_for("login"))
    return render_template("reset_password.html", token=token, username=record["username"])


@app.route("/profile")
@login_required
def profile():
    user       = current_user()
    total_xp   = db.get_total_xp(user["id"])
    level_info = get_level_info(total_xp)
    earned     = db.get_earned_badges(user["id"])
    earned_keys = {b["badge_key"] for b in earned}
    xp_log     = db.get_xp_log(user["id"], limit=20)
    stats      = db.get_stats(user["id"])
    streak     = db.get_streak_data(user["id"])
    # Group badges by category
    from gamification import BADGES
    categories = {}
    for badge in BADGES.values():
        cat = badge["category"]
        if cat not in categories:
            categories[cat] = []
        categories[cat].append({
            **badge,
            "earned": badge["key"] in earned_keys,
            "earned_at": next(
                (b["earned_at"][:10] for b in earned if b["badge_key"] == badge["key"]),
                None
            )
        })
    return render_template("profile.html",
        user=user, level_info=level_info, total_xp=total_xp,
        categories=categories, earned_keys=earned_keys,
        xp_log=xp_log, stats=stats, streak=streak,
        levels=LEVELS)


@app.route("/teacher/class/<int:class_id>/leaderboard_setting", methods=["POST"])
@teacher_required
def teacher_leaderboard_setting(class_id):
    user = current_user()
    cls  = db.get_class_by_id(class_id)
    if not cls or (user["role"] == "teacher" and cls["teacher_id"] != user["id"]):
        flash("Access denied.", "error")
        return redirect(url_for("teacher_dashboard"))
    show_xp = 1 if request.form.get("show_xp") else 0
    db.set_leaderboard_setting(class_id, show_xp)
    flash("Leaderboard setting updated.", "success")
    return redirect(url_for("teacher_class", class_id=class_id))


@app.route("/sandbox")
@login_required
def sandbox():
    user = current_user()
    return render_template("sandbox.html", user=user)


@app.route("/quiz/<slug>")
@login_required
def quiz(slug):
    user = current_user()
    module_name = get_module_from_slug(slug)
    if not module_name:
        flash("Quiz not found.", "error")
        return redirect(url_for("dashboard"))
    questions = get_quiz(module_name)
    if not questions:
        flash("No questions for this module yet.", "error")
        return redirect(url_for("dashboard"))
    import random, json
    shuffled = questions[:]
    random.shuffle(shuffled)
    # Shuffle options too, tracking correct answer index
    prepared = []
    for q in shuffled:
        opts   = list(enumerate(q["options"]))
        random.shuffle(opts)
        orig_indices, new_opts = zip(*opts)
        correct_new = list(orig_indices).index(q["answer"])
        prepared.append({
            "q":           q["q"],
            "options":     list(new_opts),
            "answer":      correct_new,
            "explanation": q["explanation"],
        })
    best = db.get_best_quiz_result(user["id"], module_name)
    attempts = db.get_quiz_attempt_count(user["id"], module_name)
    return render_template("quiz.html",
        user=user,
        module_name=module_name,
        slug=slug,
        questions=prepared,
        questions_json=json.dumps(prepared),
        best=best,
        attempts=attempts,
        pass_mark=7)


@app.route("/quiz/<slug>/submit", methods=["POST"])
@login_required
def quiz_submit(slug):
    import json
    user = current_user()
    module_name = get_module_from_slug(slug)
    if not module_name:
        return redirect(url_for("dashboard"))
    answers_raw  = request.form.get("answers", "[]")
    questions_raw = request.form.get("questions_json", "[]")
    try:
        answers   = json.loads(answers_raw)
        questions = json.loads(questions_raw)
    except Exception:
        flash("Quiz submission error. Please try again.", "error")
        return redirect(url_for("quiz", slug=slug))
    total   = len(questions)
    correct = sum(
        1 for i, q in enumerate(questions)
        if i < len(answers) and answers[i] == q["answer"]
    )
    passed = db.save_quiz_result(user["id"], module_name, correct, total, pass_mark=7)
    # Award XP for quiz
    xp_earned  = 0
    new_badges = []
    if passed:
        xp_earned, new_badges = award_xp_and_badges(
            user["id"], "quiz_pass", XP_RULES["quiz_pass"],
            note=f"{module_name}_pass"
        )
    if correct == total:  # perfect score
        bonus, nb = award_xp_and_badges(
            user["id"], "quiz_perfect", XP_RULES["quiz_perfect"],
            note=f"{module_name}_perfect"
        )
        xp_earned += bonus
        new_badges += nb
    new_badges = list(dict.fromkeys(new_badges))
    new_badge_data = [
        {"key": k, "name": BADGES[k]["name"], "icon": BADGES[k]["icon"]}
        for k in new_badges if k in BADGES
    ]
    total_xp   = db.get_total_xp(user["id"])
    level_info = get_level_info(total_xp)
    return render_template("quiz_result.html",
        user=user,
        module_name=module_name,
        slug=slug,
        score=correct,
        total=total,
        passed=passed,
        pct=round(correct / total * 100),
        answers=answers,
        questions=questions,
        pass_mark=7,
        xp_earned=xp_earned,
        new_badges=new_badge_data,
        level_info=level_info)


@app.route("/enrol", methods=["GET", "POST"])
@login_required
def enrol():
    """Student enters a 6-digit class code to join a class."""
    user = current_user()
    if user["role"] != "student":
        return redirect(url_for("dashboard"))

    # Already in a class?
    existing_class = db.get_class_for_student(user["id"])

    if request.method == "POST":
        code = request.form.get("code", "").strip().upper()
        if len(code) != 6:
            flash("Please enter a valid 6-character code.", "error")
        else:
            cls = db.get_class_by_enrol_code(code)
            if not cls:
                flash(f"Code '{code}' not found or is no longer active.", "error")
            else:
                ok, msg = db.enrol_student(user["id"], cls["id"])
                if ok:
                    flash(f"Welcome to {cls['name']}! 🎉", "success")
                    return redirect(url_for("dashboard"))
                else:
                    flash(msg, "error")

    return render_template("enrol.html",
        user=user, existing_class=existing_class)


@app.route("/admin/classes/<int:class_id>/gencode", methods=["POST"])
@admin_required
def admin_gen_code(class_id):
    code = db.set_enrol_code(class_id)
    flash(f"New enrol code generated: {code}", "success")
    return redirect(url_for("admin_classes"))


@app.route("/admin/classes/<int:class_id>/togglecode", methods=["POST"])
@admin_required
def admin_toggle_code(class_id):
    db.toggle_enrol_code(class_id)
    flash("Enrol code status toggled.", "success")
    return redirect(url_for("admin_classes"))


@app.route("/teacher/class/<int:class_id>/gencode", methods=["POST"])
@teacher_required
def teacher_gen_code(class_id):
    user = current_user()
    cls = db.get_class_by_id(class_id)
    if not cls or (user["role"] == "teacher" and cls["teacher_id"] != user["id"]):
        flash("Access denied.", "error")
        return redirect(url_for("teacher_dashboard"))
    code = db.set_enrol_code(class_id)
    flash(f"New enrol code: {code}", "success")
    return redirect(url_for("teacher_class", class_id=class_id))


@app.route("/teacher/class/<int:class_id>/togglecode", methods=["POST"])
@teacher_required
def teacher_toggle_code(class_id):
    user = current_user()
    cls = db.get_class_by_id(class_id)
    if not cls or (user["role"] == "teacher" and cls["teacher_id"] != user["id"]):
        flash("Access denied.", "error")
        return redirect(url_for("teacher_dashboard"))
    db.toggle_enrol_code(class_id)
    flash("Enrol code status updated.", "success")
    return redirect(url_for("teacher_class", class_id=class_id))


if __name__ == "__main__":
    print("\n" + "="*56)
    print("  Python 108  (v11 — Gamification · XP · Badges · Levels)")
    print("  Open: http://localhost:5000")
    print("  Run setup_admin.py first to create admin account")
    print("="*56 + "\n")
    app.run(debug=True, port=5000)