import sqlite3
import os

DB_PATH = os.environ.get(
    "DB_PATH",
    os.path.join(os.path.dirname(__file__), "python108.db")
)

def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db()
    c = conn.cursor()
    c.executescript("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            email TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            role TEXT DEFAULT 'student',
            is_active INTEGER DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        CREATE TABLE IF NOT EXISTS classes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            description TEXT DEFAULT '',
            teacher_id INTEGER,
            enrol_code TEXT UNIQUE,
            code_active INTEGER DEFAULT 1,
            is_active INTEGER DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (teacher_id) REFERENCES users(id)
        );
        CREATE TABLE IF NOT EXISTS class_students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            class_id INTEGER NOT NULL,
            student_id INTEGER NOT NULL,
            joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (class_id) REFERENCES classes(id),
            FOREIGN KEY (student_id) REFERENCES users(id),
            UNIQUE(class_id, student_id)
        );
        CREATE TABLE IF NOT EXISTS program_progress (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            program_n INTEGER NOT NULL,
            completed INTEGER DEFAULT 0,
            completed_at TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id),
            UNIQUE(user_id, program_n)
        );
        CREATE TABLE IF NOT EXISTS exercise_progress (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            exercise_n INTEGER NOT NULL,
            solved INTEGER DEFAULT 0,
            solved_at TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id),
            UNIQUE(user_id, exercise_n)
        );
        CREATE TABLE IF NOT EXISTS daily_activity (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            activity_date DATE NOT NULL,
            programs_done INTEGER DEFAULT 0,
            FOREIGN KEY (user_id) REFERENCES users(id),
            UNIQUE(user_id, activity_date)
        );
        CREATE TABLE IF NOT EXISTS quiz_results (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            module_name TEXT NOT NULL,
            score INTEGER NOT NULL,
            total INTEGER NOT NULL,
            passed INTEGER DEFAULT 0,
            attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        );
        CREATE TABLE IF NOT EXISTS program_notes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            program_n INTEGER NOT NULL,
            note TEXT DEFAULT '',
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id),
            UNIQUE(user_id, program_n)
        );
        CREATE TABLE IF NOT EXISTS bookmarks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            program_n INTEGER NOT NULL,
            bookmarked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id),
            UNIQUE(user_id, program_n)
        );
        CREATE TABLE IF NOT EXISTS assignments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            class_id INTEGER NOT NULL,
            teacher_id INTEGER NOT NULL,
            program_n INTEGER NOT NULL,
            due_date DATE NOT NULL,
            note TEXT DEFAULT '',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (class_id)   REFERENCES classes(id),
            FOREIGN KEY (teacher_id) REFERENCES users(id)
        );
        CREATE TABLE IF NOT EXISTS teacher_comments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            teacher_id INTEGER NOT NULL,
            student_id INTEGER NOT NULL,
            program_n  INTEGER NOT NULL,
            comment    TEXT DEFAULT '',
            rating     INTEGER DEFAULT 0,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (teacher_id) REFERENCES users(id),
            FOREIGN KEY (student_id) REFERENCES users(id),
            UNIQUE(teacher_id, student_id, program_n)
        );
        CREATE TABLE IF NOT EXISTS announcements (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            class_id   INTEGER NOT NULL,
            teacher_id INTEGER NOT NULL,
            title      TEXT NOT NULL,
            body       TEXT DEFAULT '',
            pinned     INTEGER DEFAULT 0,
            expires_at DATE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (class_id)   REFERENCES classes(id),
            FOREIGN KEY (teacher_id) REFERENCES users(id)
        );
        CREATE TABLE IF NOT EXISTS password_resets (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id    INTEGER NOT NULL,
            token      TEXT UNIQUE NOT NULL,
            expires_at TIMESTAMP NOT NULL,
            used       INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        );
        CREATE TABLE IF NOT EXISTS xp_log (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id    INTEGER NOT NULL,
            action     TEXT NOT NULL,
            xp_earned  INTEGER NOT NULL,
            note       TEXT DEFAULT '',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        );
        CREATE TABLE IF NOT EXISTS user_badges (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id    INTEGER NOT NULL,
            badge_key  TEXT NOT NULL,
            earned_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id),
            UNIQUE(user_id, badge_key)
        );
        CREATE TABLE IF NOT EXISTS class_leaderboard_settings (
            class_id       INTEGER PRIMARY KEY,
            show_xp        INTEGER DEFAULT 1,
            FOREIGN KEY (class_id) REFERENCES classes(id)
        );
    """)
    conn.commit()
    conn.close()

# ── User helpers ─────────────────────────────────────────────────
def get_user_by_username(username):
    conn = get_db()
    u = conn.execute("SELECT * FROM users WHERE username=?", (username,)).fetchone()
    conn.close()
    return u

def get_user_by_id(user_id):
    conn = get_db()
    u = conn.execute("SELECT * FROM users WHERE id=?", (user_id,)).fetchone()
    conn.close()
    return u

def create_user(username, email, password_hash, role="student"):
    conn = get_db()
    try:
        conn.execute(
            "INSERT INTO users (username,email,password_hash,role) VALUES (?,?,?,?)",
            (username, email, password_hash, role)
        )
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False
    finally:
        conn.close()

def get_all_users(role=None):
    conn = get_db()
    if role:
        rows = conn.execute(
            "SELECT * FROM users WHERE role=? ORDER BY created_at DESC", (role,)
        ).fetchall()
    else:
        rows = conn.execute("SELECT * FROM users ORDER BY created_at DESC").fetchall()
    conn.close()
    return rows

def set_user_active(user_id, active):
    conn = get_db()
    conn.execute("UPDATE users SET is_active=? WHERE id=?", (active, user_id))
    conn.commit()
    conn.close()

def update_password(user_id, new_hash):
    conn = get_db()
    conn.execute("UPDATE users SET password_hash=? WHERE id=?", (new_hash, user_id))
    conn.commit()
    conn.close()

# ── Class helpers ────────────────────────────────────────────────
def create_class(name, description, teacher_id=None):
    conn = get_db()
    conn.execute(
        "INSERT INTO classes (name,description,teacher_id) VALUES (?,?,?)",
        (name, description, teacher_id)
    )
    conn.commit()
    conn.close()

def get_all_classes():
    conn = get_db()
    rows = conn.execute("""
        SELECT c.*, u.username as teacher_name,
               COUNT(DISTINCT cs.student_id) as student_count
        FROM classes c
        LEFT JOIN users u ON c.teacher_id=u.id
        LEFT JOIN class_students cs ON c.id=cs.class_id
        GROUP BY c.id ORDER BY c.created_at DESC
    """).fetchall()
    conn.close()
    return rows

def get_class_by_id(class_id):
    conn = get_db()
    row = conn.execute("""
        SELECT c.*, u.username as teacher_name
        FROM classes c LEFT JOIN users u ON c.teacher_id=u.id
        WHERE c.id=?
    """, (class_id,)).fetchone()
    conn.close()
    return row

def get_classes_for_teacher(teacher_id):
    conn = get_db()
    rows = conn.execute("""
        SELECT c.*, COUNT(DISTINCT cs.student_id) as student_count
        FROM classes c LEFT JOIN class_students cs ON c.id=cs.class_id
        WHERE c.teacher_id=? AND c.is_active=1
        GROUP BY c.id ORDER BY c.created_at DESC
    """, (teacher_id,)).fetchall()
    conn.close()
    return rows

def get_class_for_student(student_id):
    conn = get_db()
    row = conn.execute("""
        SELECT c.* FROM classes c
        JOIN class_students cs ON c.id=cs.class_id
        WHERE cs.student_id=? LIMIT 1
    """, (student_id,)).fetchone()
    conn.close()
    return row

def assign_teacher_to_class(class_id, teacher_id):
    conn = get_db()
    conn.execute("UPDATE classes SET teacher_id=? WHERE id=?", (teacher_id, class_id))
    conn.commit()
    conn.close()

def set_class_active(class_id, active):
    conn = get_db()
    conn.execute("UPDATE classes SET is_active=? WHERE id=?", (active, class_id))
    conn.commit()
    conn.close()

def get_students_in_class(class_id):
    conn = get_db()
    rows = conn.execute("""
        SELECT u.* FROM users u
        JOIN class_students cs ON u.id=cs.student_id
        WHERE cs.class_id=? AND u.is_active=1
        ORDER BY u.username
    """, (class_id,)).fetchall()
    conn.close()
    return rows

def assign_student_to_class(student_id, class_id):
    conn = get_db()
    conn.execute("DELETE FROM class_students WHERE student_id=?", (student_id,))
    conn.execute(
        "INSERT OR IGNORE INTO class_students (class_id,student_id) VALUES (?,?)",
        (class_id, student_id)
    )
    conn.commit()
    conn.close()

def remove_student_from_class(student_id, class_id):
    conn = get_db()
    conn.execute(
        "DELETE FROM class_students WHERE student_id=? AND class_id=?",
        (student_id, class_id)
    )
    conn.commit()
    conn.close()

def get_unassigned_students():
    conn = get_db()
    rows = conn.execute("""
        SELECT u.* FROM users u
        LEFT JOIN class_students cs ON u.id=cs.student_id
        WHERE u.role='student' AND u.is_active=1 AND cs.student_id IS NULL
        ORDER BY u.username
    """).fetchall()
    conn.close()
    return rows

# ── Enrol code helpers ───────────────────────────────────────────
def generate_enrol_code():
    """Generate a unique 6-character alphanumeric code."""
    import random, string
    while True:
        code = ''.join(random.choices(string.ascii_uppercase + string.digits, k=6))
        conn = get_db()
        existing = conn.execute(
            "SELECT id FROM classes WHERE enrol_code=?", (code,)
        ).fetchone()
        conn.close()
        if not existing:
            return code

def set_enrol_code(class_id):
    """Generate and save a new enrol code for a class. Returns the code."""
    code = generate_enrol_code()
    conn = get_db()
    conn.execute(
        "UPDATE classes SET enrol_code=?, code_active=1 WHERE id=?",
        (code, class_id)
    )
    conn.commit()
    conn.close()
    return code

def toggle_enrol_code(class_id):
    """Enable or disable the enrol code for a class."""
    conn = get_db()
    cls = conn.execute(
        "SELECT code_active FROM classes WHERE id=?", (class_id,)
    ).fetchone()
    if cls:
        new_val = 0 if cls["code_active"] else 1
        conn.execute(
            "UPDATE classes SET code_active=? WHERE id=?",
            (new_val, class_id)
        )
        conn.commit()
    conn.close()

def get_class_by_enrol_code(code):
    """Look up a class by its enrol code (only if active)."""
    conn = get_db()
    row = conn.execute(
        "SELECT * FROM classes WHERE enrol_code=? AND code_active=1 AND is_active=1",
        (code.strip().upper(),)
    ).fetchone()
    conn.close()
    return row

def enrol_student(student_id, class_id):
    """Assign student to class via enrol code. Returns (ok, message)."""
    # Check student not already in a class
    conn = get_db()
    existing = conn.execute(
        "SELECT class_id FROM class_students WHERE student_id=?",
        (student_id,)
    ).fetchone()
    if existing:
        if existing["class_id"] == class_id:
            conn.close()
            return False, "You are already in this class."
        conn.close()
        return False, "You are already enrolled in another class. Contact your admin."
    conn.execute(
        "INSERT OR IGNORE INTO class_students (class_id, student_id) VALUES (?,?)",
        (class_id, student_id)
    )
    conn.commit()
    conn.close()
    return True, "Successfully enrolled!"

# ── Progress helpers ─────────────────────────────────────────────
def get_program_progress(user_id):
    conn = get_db()
    rows = conn.execute(
        "SELECT program_n,completed FROM program_progress WHERE user_id=?", (user_id,)
    ).fetchall()
    conn.close()
    return {r["program_n"]: r["completed"] for r in rows}

def toggle_program_complete(user_id, program_n):
    conn = get_db()
    ex = conn.execute(
        "SELECT completed FROM program_progress WHERE user_id=? AND program_n=?",
        (user_id, program_n)
    ).fetchone()
    if ex:
        new_val = 0 if ex["completed"] else 1
        conn.execute(
            "UPDATE program_progress SET completed=?, completed_at=CASE WHEN ? THEN CURRENT_TIMESTAMP ELSE NULL END WHERE user_id=? AND program_n=?",
            (new_val, new_val, user_id, program_n)
        )
    else:
        conn.execute(
            "INSERT INTO program_progress (user_id,program_n,completed,completed_at) VALUES (?,?,1,CURRENT_TIMESTAMP)",
            (user_id, program_n)
        )
    conn.commit()
    conn.close()

def get_exercise_progress(user_id):
    conn = get_db()
    rows = conn.execute(
        "SELECT exercise_n,solved FROM exercise_progress WHERE user_id=?", (user_id,)
    ).fetchall()
    conn.close()
    return {r["exercise_n"]: r["solved"] for r in rows}

def toggle_exercise_solved(user_id, exercise_n):
    conn = get_db()
    ex = conn.execute(
        "SELECT solved FROM exercise_progress WHERE user_id=? AND exercise_n=?",
        (user_id, exercise_n)
    ).fetchone()
    if ex:
        new_val = 0 if ex["solved"] else 1
        conn.execute(
            "UPDATE exercise_progress SET solved=?, solved_at=CURRENT_TIMESTAMP WHERE user_id=? AND exercise_n=?",
            (new_val, user_id, exercise_n)
        )
    else:
        conn.execute(
            "INSERT INTO exercise_progress (user_id,exercise_n,solved,solved_at) VALUES (?,?,1,CURRENT_TIMESTAMP)",
            (user_id, exercise_n)
        )
    conn.commit()
    conn.close()

def get_stats(user_id):
    conn = get_db()
    prog = conn.execute(
        "SELECT COUNT(*) as c FROM program_progress WHERE user_id=? AND completed=1", (user_id,)
    ).fetchone()["c"]
    ex = conn.execute(
        "SELECT COUNT(*) as c FROM exercise_progress WHERE user_id=? AND solved=1", (user_id,)
    ).fetchone()["c"]
    conn.close()
    return {"programs_done": prog, "exercises_done": ex}

def get_class_leaderboard(class_id, limit=5):
    conn = get_db()
    rows = conn.execute("""
        SELECT u.id, u.username, COUNT(pp.program_n) as programs_done
        FROM users u
        JOIN class_students cs ON u.id=cs.student_id
        LEFT JOIN program_progress pp ON u.id=pp.user_id AND pp.completed=1
        WHERE cs.class_id=? AND u.is_active=1
        GROUP BY u.id ORDER BY programs_done DESC LIMIT ?
    """, (class_id, limit)).fetchall()
    conn.close()
    return rows

def get_class_progress_matrix(class_id):
    conn = get_db()
    students = get_students_in_class(class_id)
    result = {}
    for s in students:
        rows = conn.execute(
            "SELECT program_n FROM program_progress WHERE user_id=? AND completed=1",
            (s["id"],)
        ).fetchall()
        result[s["id"]] = {r["program_n"] for r in rows}
    conn.close()
    return result

def get_student_summary_for_class(class_id):
    conn = get_db()
    rows = conn.execute("""
        SELECT u.id, u.username, u.created_at,
               COUNT(DISTINCT pp.program_n) as prog_done,
               COUNT(DISTINCT ep.exercise_n) as ex_done
        FROM users u
        JOIN class_students cs ON u.id=cs.student_id
        LEFT JOIN program_progress pp ON u.id=pp.user_id AND pp.completed=1
        LEFT JOIN exercise_progress ep ON u.id=ep.user_id AND ep.solved=1
        WHERE cs.class_id=? AND u.is_active=1
        GROUP BY u.id ORDER BY prog_done DESC
    """, (class_id,)).fetchall()
    conn.close()
    return rows

def get_site_stats():
    conn = get_db()
    s = conn.execute("SELECT COUNT(*) as c FROM users WHERE role='student' AND is_active=1").fetchone()["c"]
    t = conn.execute("SELECT COUNT(*) as c FROM users WHERE role='teacher' AND is_active=1").fetchone()["c"]
    cl = conn.execute("SELECT COUNT(*) as c FROM classes WHERE is_active=1").fetchone()["c"]
    cp = conn.execute("SELECT COUNT(*) as c FROM program_progress WHERE completed=1").fetchone()["c"]
    conn.close()
    return {"students": s, "teachers": t, "classes": cl, "completions": cp}

def get_top_students(limit=10):
    conn = get_db()
    rows = conn.execute("""
        SELECT u.id, u.username, COUNT(pp.program_n) as prog_done,
               ROUND(COUNT(pp.program_n)*100.0/108,1) as pct
        FROM users u
        LEFT JOIN program_progress pp ON u.id=pp.user_id AND pp.completed=1
        WHERE u.role='student' AND u.is_active=1
        GROUP BY u.id ORDER BY prog_done DESC LIMIT ?
    """, (limit,)).fetchall()
    conn.close()
    return rows

def export_class_csv(class_id):
    """Returns list of dicts for CSV export."""
    conn = get_db()
    rows = conn.execute("""
        SELECT u.username, u.email, u.created_at,
               COUNT(DISTINCT pp.program_n) as prog_done,
               COUNT(DISTINCT ep.exercise_n) as ex_done,
               ROUND(COUNT(DISTINCT pp.program_n)*100.0/108,1) as pct
        FROM users u
        JOIN class_students cs ON u.id=cs.student_id
        LEFT JOIN program_progress pp ON u.id=pp.user_id AND pp.completed=1
        LEFT JOIN exercise_progress ep ON u.id=ep.user_id AND ep.solved=1
        WHERE cs.class_id=? AND u.is_active=1
        GROUP BY u.id ORDER BY prog_done DESC
    """, (class_id,)).fetchall()
    conn.close()
    return [dict(r) for r in rows]

# ── Streak & activity helpers ────────────────────────────────────
def record_activity(user_id):
    """Called whenever a student completes a program. Upserts today's count."""
    from datetime import date
    today = date.today().isoformat()
    conn = get_db()
    existing = conn.execute(
        "SELECT programs_done FROM daily_activity WHERE user_id=? AND activity_date=?",
        (user_id, today)
    ).fetchone()
    if existing:
        conn.execute(
            "UPDATE daily_activity SET programs_done=programs_done+1 WHERE user_id=? AND activity_date=?",
            (user_id, today)
        )
    else:
        conn.execute(
            "INSERT INTO daily_activity (user_id, activity_date, programs_done) VALUES (?,?,1)",
            (user_id, today)
        )
    conn.commit()
    conn.close()

def get_streak_data(user_id):
    """
    Returns dict with:
      current_streak  — consecutive days up to and including today
      longest_streak  — all-time best
      studied_today   — bool
      total_days      — total active days ever
    """
    from datetime import date, timedelta
    conn = get_db()
    rows = conn.execute(
        "SELECT activity_date, programs_done FROM daily_activity WHERE user_id=? AND programs_done>0 ORDER BY activity_date DESC",
        (user_id,)
    ).fetchall()
    conn.close()

    if not rows:
        return {"current_streak": 0, "longest_streak": 0,
                "studied_today": False, "total_days": 0}

    dates = [date.fromisoformat(r["activity_date"]) for r in rows]
    today = date.today()

    studied_today = dates[0] == today

    # Current streak — count backwards from today (or yesterday if not studied today)
    current_streak = 0
    check = today if studied_today else today - timedelta(days=1)
    for d in dates:
        if d == check:
            current_streak += 1
            check -= timedelta(days=1)
        elif d < check:
            break

    # Longest streak — scan full sorted list
    sorted_dates = sorted(set(dates))
    longest = 1
    run = 1
    for i in range(1, len(sorted_dates)):
        if sorted_dates[i] - sorted_dates[i-1] == timedelta(days=1):
            run += 1
            longest = max(longest, run)
        else:
            run = 1

    return {
        "current_streak": current_streak,
        "longest_streak": max(longest, current_streak),
        "studied_today": studied_today,
        "total_days": len(dates),
    }

def get_activity_last_n_days(user_id, n=30):
    """Returns list of {date, programs_done} for the last n days (including zeros)."""
    from datetime import date, timedelta
    conn = get_db()
    rows = conn.execute(
        "SELECT activity_date, programs_done FROM daily_activity WHERE user_id=? AND activity_date >= date('now', ?)",
        (user_id, f"-{n} days")
    ).fetchall()
    conn.close()

    activity_map = {r["activity_date"]: r["programs_done"] for r in rows}
    today = date.today()
    result = []
    for i in range(n - 1, -1, -1):
        d = today - timedelta(days=i)
        ds = d.isoformat()
        result.append({
            "date": ds,
            "day": d.day,
            "weekday": d.strftime("%a"),
            "month": d.strftime("%b"),
            "programs_done": activity_map.get(ds, 0),
            "is_today": d == today,
        })
    return result

def get_class_streaks(class_id):
    """For teacher view — current streak per student in a class."""
    students = get_students_in_class(class_id)
    result = []
    for s in students:
        sd = get_streak_data(s["id"])
        result.append({
            "username": s["username"],
            "id": s["id"],
            "current_streak": sd["current_streak"],
            "longest_streak": sd["longest_streak"],
            "studied_today": sd["studied_today"],
        })
    return sorted(result, key=lambda x: -x["current_streak"])

# ── Quiz helpers ─────────────────────────────────────────────────
def save_quiz_result(user_id, module_name, score, total, pass_mark=7):
    passed = 1 if score >= pass_mark else 0
    conn = get_db()
    conn.execute(
        "INSERT INTO quiz_results (user_id, module_name, score, total, passed) VALUES (?,?,?,?,?)",
        (user_id, module_name, score, total, passed)
    )
    conn.commit()
    conn.close()
    return passed

def get_best_quiz_result(user_id, module_name):
    """Returns the best (highest score) result for a user/module."""
    conn = get_db()
    row = conn.execute("""
        SELECT score, total, passed, attempted_at
        FROM quiz_results
        WHERE user_id=? AND module_name=?
        ORDER BY score DESC, attempted_at DESC
        LIMIT 1
    """, (user_id, module_name)).fetchone()
    conn.close()
    return row

def get_all_quiz_results(user_id):
    """Returns {module_name: {score, total, passed}} for all modules attempted."""
    conn = get_db()
    rows = conn.execute("""
        SELECT module_name, MAX(score) as score, total, MAX(passed) as passed
        FROM quiz_results WHERE user_id=?
        GROUP BY module_name
    """, (user_id,)).fetchall()
    conn.close()
    return {r["module_name"]: dict(r) for r in rows}

def get_quiz_attempt_count(user_id, module_name):
    conn = get_db()
    count = conn.execute(
        "SELECT COUNT(*) as c FROM quiz_results WHERE user_id=? AND module_name=?",
        (user_id, module_name)
    ).fetchone()["c"]
    conn.close()
    return count

def get_class_quiz_summary(class_id):
    """For teacher: per-student quiz pass counts."""
    conn = get_db()
    rows = conn.execute("""
        SELECT u.id, u.username,
               COUNT(DISTINCT CASE WHEN qr.passed=1 THEN qr.module_name END) as modules_passed,
               COUNT(DISTINCT qr.module_name) as modules_attempted
        FROM users u
        JOIN class_students cs ON u.id=cs.student_id
        LEFT JOIN quiz_results qr ON u.id=qr.user_id
        WHERE cs.class_id=? AND u.is_active=1
        GROUP BY u.id ORDER BY modules_passed DESC
    """, (class_id,)).fetchall()
    conn.close()
    return rows

# ── Notes helpers ────────────────────────────────────────────────
def get_note(user_id, program_n):
    conn = get_db()
    row = conn.execute(
        "SELECT note FROM program_notes WHERE user_id=? AND program_n=?",
        (user_id, program_n)
    ).fetchone()
    conn.close()
    return row["note"] if row else ""

def save_note(user_id, program_n, note):
    conn = get_db()
    conn.execute("""
        INSERT INTO program_notes (user_id, program_n, note, updated_at)
        VALUES (?,?,?,CURRENT_TIMESTAMP)
        ON CONFLICT(user_id, program_n)
        DO UPDATE SET note=excluded.note, updated_at=CURRENT_TIMESTAMP
    """, (user_id, program_n, note.strip()))
    conn.commit()
    conn.close()

def get_all_notes(user_id):
    conn = get_db()
    rows = conn.execute(
        "SELECT program_n, note FROM program_notes WHERE user_id=? AND note != '' ORDER BY program_n",
        (user_id,)
    ).fetchall()
    conn.close()
    return {r["program_n"]: r["note"] for r in rows}

# ── Bookmark helpers ─────────────────────────────────────────────
def get_bookmarks(user_id):
    conn = get_db()
    rows = conn.execute(
        "SELECT program_n FROM bookmarks WHERE user_id=? ORDER BY bookmarked_at DESC",
        (user_id,)
    ).fetchall()
    conn.close()
    return {r["program_n"] for r in rows}

def toggle_bookmark(user_id, program_n):
    conn = get_db()
    existing = conn.execute(
        "SELECT id FROM bookmarks WHERE user_id=? AND program_n=?",
        (user_id, program_n)
    ).fetchone()
    if existing:
        conn.execute(
            "DELETE FROM bookmarks WHERE user_id=? AND program_n=?",
            (user_id, program_n)
        )
        is_bookmarked = False
    else:
        conn.execute(
            "INSERT INTO bookmarks (user_id, program_n) VALUES (?,?)",
            (user_id, program_n)
        )
        is_bookmarked = True
    conn.commit()
    conn.close()
    return is_bookmarked

# ── Assignment helpers ───────────────────────────────────────────
def create_assignment(class_id, teacher_id, program_n, due_date, note=""):
    conn = get_db()
    conn.execute(
        "INSERT INTO assignments (class_id,teacher_id,program_n,due_date,note) VALUES (?,?,?,?,?)",
        (class_id, teacher_id, program_n, due_date, note)
    )
    conn.commit()
    conn.close()

def get_assignments_for_class(class_id):
    conn = get_db()
    rows = conn.execute("""
        SELECT a.*, u.username as teacher_name
        FROM assignments a
        JOIN users u ON a.teacher_id=u.id
        WHERE a.class_id=?
        ORDER BY a.due_date ASC
    """, (class_id,)).fetchall()
    conn.close()
    return rows

def delete_assignment(assignment_id, teacher_id):
    conn = get_db()
    conn.execute(
        "DELETE FROM assignments WHERE id=? AND teacher_id=?",
        (assignment_id, teacher_id)
    )
    conn.commit()
    conn.close()

def get_assignments_for_student(student_id):
    """Returns assignments for the student's class, with completion status."""
    from datetime import date
    conn = get_db()
    rows = conn.execute("""
        SELECT a.*, c.name as class_name,
               pp.completed,
               CASE WHEN a.due_date < date('now') AND (pp.completed IS NULL OR pp.completed=0)
                    THEN 1 ELSE 0 END as overdue
        FROM assignments a
        JOIN class_students cs ON a.class_id=cs.class_id
        JOIN classes c ON a.class_id=c.id
        LEFT JOIN program_progress pp
               ON pp.user_id=? AND pp.program_n=a.program_n
        WHERE cs.student_id=?
        ORDER BY a.due_date ASC
    """, (student_id, student_id)).fetchall()
    conn.close()
    return rows

def get_assignment_status_for_class(class_id):
    """For teacher: per-student completion of each assignment."""
    conn = get_db()
    assignments = conn.execute(
        "SELECT * FROM assignments WHERE class_id=? ORDER BY due_date ASC",
        (class_id,)
    ).fetchall()
    students = get_students_in_class(class_id)
    status = {}
    for s in students:
        status[s["id"]] = {}
        for a in assignments:
            done = conn.execute(
                "SELECT completed FROM program_progress WHERE user_id=? AND program_n=? AND completed=1",
                (s["id"], a["program_n"])
            ).fetchone()
            status[s["id"]][a["id"]] = bool(done)
    conn.close()
    return assignments, status

# ── Teacher comment helpers ──────────────────────────────────────
def save_comment(teacher_id, student_id, program_n, comment, rating):
    conn = get_db()
    conn.execute("""
        INSERT INTO teacher_comments (teacher_id,student_id,program_n,comment,rating,updated_at)
        VALUES (?,?,?,?,?,CURRENT_TIMESTAMP)
        ON CONFLICT(teacher_id,student_id,program_n)
        DO UPDATE SET comment=excluded.comment,
                      rating=excluded.rating,
                      updated_at=CURRENT_TIMESTAMP
    """, (teacher_id, student_id, program_n, comment.strip(), rating))
    conn.commit()
    conn.close()

def get_comment(teacher_id, student_id, program_n):
    conn = get_db()
    row = conn.execute(
        "SELECT * FROM teacher_comments WHERE teacher_id=? AND student_id=? AND program_n=?",
        (teacher_id, student_id, program_n)
    ).fetchone()
    conn.close()
    return row

def get_comments_for_student(student_id):
    """All comments left on this student's programs, with teacher name."""
    conn = get_db()
    rows = conn.execute("""
        SELECT tc.*, u.username as teacher_name
        FROM teacher_comments tc
        JOIN users u ON tc.teacher_id=u.id
        WHERE tc.student_id=? AND tc.comment != ''
        ORDER BY tc.updated_at DESC
    """, (student_id,)).fetchall()
    conn.close()
    return rows

def get_comments_for_program(student_id, program_n):
    conn = get_db()
    rows = conn.execute("""
        SELECT tc.*, u.username as teacher_name
        FROM teacher_comments tc
        JOIN users u ON tc.teacher_id=u.id
        WHERE tc.student_id=? AND tc.program_n=?
        ORDER BY tc.updated_at DESC
    """, (student_id, program_n)).fetchall()
    conn.close()
    return rows

def delete_comment(teacher_id, student_id, program_n):
    conn = get_db()
    conn.execute(
        "DELETE FROM teacher_comments WHERE teacher_id=? AND student_id=? AND program_n=?",
        (teacher_id, student_id, program_n)
    )
    conn.commit()
    conn.close()

# ── Weak spots detector ──────────────────────────────────────────
def get_weak_spots(user_id, modules_list):
    """
    Returns list of {mod, prog_pct, quiz_score, quiz_passed, weakness_score}
    sorted weakest first. weakness_score 0=strongest, 100=weakest.
    """
    conn = get_db()
    result = []
    for mod_name in modules_list:
        # Program completion %
        prog_ns = conn.execute(
            "SELECT COUNT(*) as total FROM program_progress WHERE user_id=? AND program_n IN "
            "(SELECT program_n FROM program_progress WHERE user_id=?)",
            (user_id, user_id)
        ).fetchone()  # fallback below

        # Better: get from ALL_PROGRAMS data
        result.append({"mod": mod_name})

    conn.close()

    # We'll compute this properly in the route using ALL_PROGRAMS
    return result

def get_module_weak_spots(user_id, all_programs, modules_list):
    """
    Returns list of module dicts with completion and quiz data, sorted weakest first.
    """
    conn = get_db()
    prog_done = conn.execute(
        "SELECT program_n FROM program_progress WHERE user_id=? AND completed=1",
        (user_id,)
    ).fetchall()
    done_set = {r["program_n"] for r in prog_done}

    quiz_rows = conn.execute(
        "SELECT module_name, MAX(score) as score, total, MAX(passed) as passed "
        "FROM quiz_results WHERE user_id=? GROUP BY module_name",
        (user_id,)
    ).fetchall()
    quiz_map = {r["module_name"]: r for r in quiz_rows}
    conn.close()

    spots = []
    for mod_name in modules_list:
        prog_ns  = [p["n"] for p in all_programs if p["mod"] == mod_name]
        total    = len(prog_ns)
        done     = sum(1 for n in prog_ns if n in done_set)
        prog_pct = round(done / total * 100) if total else 0

        qr = quiz_map.get(mod_name)
        quiz_pct    = round(qr["score"] / qr["total"] * 100) if qr else None
        quiz_passed = bool(qr["passed"]) if qr else False

        # Weakness score: low prog completion + low quiz = high weakness
        prog_weight = (100 - prog_pct) * 0.5
        quiz_weight = (100 - quiz_pct) * 0.5 if quiz_pct is not None else 40
        weakness    = round(prog_weight + quiz_weight)

        spots.append({
            "mod":          mod_name,
            "prog_done":    done,
            "prog_total":   total,
            "prog_pct":     prog_pct,
            "quiz_pct":     quiz_pct,
            "quiz_passed":  quiz_passed,
            "weakness":     weakness,
        })

    return sorted(spots, key=lambda x: -x["weakness"])

# ── Bulk import helpers ──────────────────────────────────────────
def bulk_import_students(rows, password_hasher):
    """
    rows: list of dicts with keys: username, email, password, class_name (optional)
    password_hasher: callable (plain_text) -> hash
    Returns: list of {username, status, message}
    """
    results = []
    conn = get_db()

    # Pre-load class name → id map
    class_rows = conn.execute("SELECT id, name FROM classes WHERE is_active=1").fetchall()
    class_map  = {r["name"].strip().lower(): r["id"] for r in class_rows}

    for row in rows:
        username   = (row.get("username")   or "").strip()
        email      = (row.get("email")      or "").strip()
        password   = (row.get("password")   or "").strip()
        class_name = (row.get("class_name") or "").strip()

        # Validate
        if not username or not email or not password:
            results.append({"username": username or "(blank)",
                            "status": "skipped",
                            "message": "Missing username, email or password"})
            continue
        if len(username) < 2:
            results.append({"username": username, "status": "skipped",
                            "message": "Username must be at least 2 characters"})
            continue
        if len(password) < 6:
            results.append({"username": username, "status": "skipped",
                            "message": "Password must be at least 6 characters"})
            continue

        # Create user
        try:
            pw_hash = password_hasher(password)
            conn.execute(
                "INSERT INTO users (username,email,password_hash,role) VALUES (?,?,?,'student')",
                (username, email, pw_hash)
            )
            conn.commit()
            user_id = conn.execute(
                "SELECT id FROM users WHERE username=?", (username,)
            ).fetchone()["id"]

            # Assign to class if provided
            class_msg = ""
            if class_name:
                cid = class_map.get(class_name.lower())
                if cid:
                    conn.execute(
                        "INSERT OR IGNORE INTO class_students (class_id,student_id) VALUES (?,?)",
                        (cid, user_id)
                    )
                    conn.commit()
                    class_msg = f" → assigned to '{class_name}'"
                else:
                    class_msg = f" (class '{class_name}' not found — not assigned)"

            results.append({"username": username, "status": "created",
                            "message": f"Account created{class_msg}"})
        except Exception as e:
            results.append({"username": username, "status": "failed",
                            "message": str(e)})

    conn.close()
    return results

def get_completion_date(user_id):
    """Returns the date of the 108th program completion, or None."""
    conn = get_db()
    row = conn.execute("""
        SELECT completed_at FROM program_progress
        WHERE user_id=? AND completed=1
        ORDER BY completed_at DESC LIMIT 1
    """, (user_id,)).fetchone()
    conn.close()
    return row["completed_at"][:10] if row and row["completed_at"] else None

def is_course_complete(user_id):
    conn = get_db()
    count = conn.execute(
        "SELECT COUNT(*) as c FROM program_progress WHERE user_id=? AND completed=1",
        (user_id,)
    ).fetchone()["c"]
    conn.close()
    return count >= 108

# ── Announcement helpers ─────────────────────────────────────────
def create_announcement(class_id, teacher_id, title, body, pinned=0, expires_at=None):
    conn = get_db()
    conn.execute(
        "INSERT INTO announcements (class_id,teacher_id,title,body,pinned,expires_at) VALUES (?,?,?,?,?,?)",
        (class_id, teacher_id, title, body, pinned, expires_at)
    )
    conn.commit()
    conn.close()

def get_announcements_for_class(class_id, active_only=True):
    conn = get_db()
    if active_only:
        rows = conn.execute("""
            SELECT a.*, u.username as teacher_name
            FROM announcements a JOIN users u ON a.teacher_id=u.id
            WHERE a.class_id=?
              AND (a.expires_at IS NULL OR a.expires_at >= date('now'))
            ORDER BY a.pinned DESC, a.created_at DESC
        """, (class_id,)).fetchall()
    else:
        rows = conn.execute("""
            SELECT a.*, u.username as teacher_name
            FROM announcements a JOIN users u ON a.teacher_id=u.id
            WHERE a.class_id=?
            ORDER BY a.pinned DESC, a.created_at DESC
        """, (class_id,)).fetchall()
    conn.close()
    return rows

def get_announcements_for_student(student_id):
    conn = get_db()
    rows = conn.execute("""
        SELECT a.*, u.username as teacher_name, c.name as class_name
        FROM announcements a
        JOIN users u ON a.teacher_id=u.id
        JOIN classes c ON a.class_id=c.id
        JOIN class_students cs ON a.class_id=cs.class_id
        WHERE cs.student_id=?
          AND (a.expires_at IS NULL OR a.expires_at >= date('now'))
        ORDER BY a.pinned DESC, a.created_at DESC
    """, (student_id,)).fetchall()
    conn.close()
    return rows

def get_all_announcements():
    conn = get_db()
    rows = conn.execute("""
        SELECT a.*, u.username as teacher_name, c.name as class_name
        FROM announcements a
        JOIN users u ON a.teacher_id=u.id
        JOIN classes c ON a.class_id=c.id
        ORDER BY a.created_at DESC
    """).fetchall()
    conn.close()
    return rows

def update_announcement(ann_id, title, body, pinned, expires_at):
    conn = get_db()
    conn.execute(
        "UPDATE announcements SET title=?,body=?,pinned=?,expires_at=? WHERE id=?",
        (title, body, pinned, expires_at, ann_id)
    )
    conn.commit()
    conn.close()

def delete_announcement(ann_id):
    conn = get_db()
    conn.execute("DELETE FROM announcements WHERE id=?", (ann_id,))
    conn.commit()
    conn.close()

# ── Password reset helpers ───────────────────────────────────────
def create_password_reset_token(user_id):
    import secrets
    from datetime import datetime, timedelta
    token      = secrets.token_urlsafe(32)
    expires_at = (datetime.now() + timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")
    conn = get_db()
    # Invalidate old tokens for this user
    conn.execute("UPDATE password_resets SET used=1 WHERE user_id=?", (user_id,))
    conn.execute(
        "INSERT INTO password_resets (user_id,token,expires_at) VALUES (?,?,?)",
        (user_id, token, expires_at)
    )
    conn.commit()
    conn.close()
    return token

def get_valid_reset_token(token):
    conn = get_db()
    row = conn.execute("""
        SELECT pr.*, u.username, u.email
        FROM password_resets pr
        JOIN users u ON pr.user_id=u.id
        WHERE pr.token=? AND pr.used=0
          AND pr.expires_at > datetime('now')
    """, (token,)).fetchone()
    conn.close()
    return row

def use_reset_token(token, new_password_hash):
    conn = get_db()
    row = conn.execute(
        "SELECT user_id FROM password_resets WHERE token=?", (token,)
    ).fetchone()
    if row:
        conn.execute("UPDATE users SET password_hash=? WHERE id=?",
                     (new_password_hash, row["user_id"]))
        conn.execute("UPDATE password_resets SET used=1 WHERE token=?", (token,))
        conn.commit()
    conn.close()
    return bool(row)

# ── XP helpers ───────────────────────────────────────────────────
def award_xp(user_id, action, xp, note=""):
    conn = get_db()
    conn.execute(
        "INSERT INTO xp_log (user_id, action, xp_earned, note) VALUES (?,?,?,?)",
        (user_id, action, xp, note)
    )
    conn.commit()
    conn.close()

def get_total_xp(user_id):
    conn = get_db()
    row = conn.execute(
        "SELECT COALESCE(SUM(xp_earned),0) as total FROM xp_log WHERE user_id=?",
        (user_id,)
    ).fetchone()
    conn.close()
    return row["total"]

def get_xp_log(user_id, limit=20):
    conn = get_db()
    rows = conn.execute(
        "SELECT * FROM xp_log WHERE user_id=? ORDER BY created_at DESC LIMIT ?",
        (user_id, limit)
    ).fetchall()
    conn.close()
    return rows

def has_earned_xp_for(user_id, action, note=""):
    """Check if a specific XP action+note was already awarded (prevents double-awarding)."""
    conn = get_db()
    row = conn.execute(
        "SELECT id FROM xp_log WHERE user_id=? AND action=? AND note=?",
        (user_id, action, note)
    ).fetchone()
    conn.close()
    return row is not None

# ── Badge helpers ────────────────────────────────────────────────
def award_badge(user_id, badge_key):
    conn = get_db()
    try:
        conn.execute(
            "INSERT INTO user_badges (user_id, badge_key) VALUES (?,?)",
            (user_id, badge_key)
        )
        conn.commit()
    except Exception:
        pass  # already earned
    finally:
        conn.close()

def get_earned_badges(user_id):
    conn = get_db()
    rows = conn.execute(
        "SELECT * FROM user_badges WHERE user_id=? ORDER BY earned_at DESC",
        (user_id,)
    ).fetchall()
    conn.close()
    return rows

# ── Leaderboard visibility helpers ───────────────────────────────
def get_leaderboard_setting(class_id):
    conn = get_db()
    row = conn.execute(
        "SELECT show_xp FROM class_leaderboard_settings WHERE class_id=?",
        (class_id,)
    ).fetchone()
    conn.close()
    return row["show_xp"] if row else 1  # default: show XP

def set_leaderboard_setting(class_id, show_xp):
    conn = get_db()
    conn.execute("""
        INSERT INTO class_leaderboard_settings (class_id, show_xp) VALUES (?,?)
        ON CONFLICT(class_id) DO UPDATE SET show_xp=excluded.show_xp
    """, (class_id, show_xp))
    conn.commit()
    conn.close()

def get_class_xp_leaderboard(class_id, limit=10):
    conn = get_db()
    rows = conn.execute("""
        SELECT u.id, u.username,
               COALESCE(SUM(xp.xp_earned),0) as total_xp,
               COUNT(DISTINCT pp.program_n) as prog_done
        FROM users u
        JOIN class_students cs ON u.id=cs.student_id
        LEFT JOIN xp_log xp ON u.id=xp.user_id
        LEFT JOIN program_progress pp ON u.id=pp.user_id AND pp.completed=1
        WHERE cs.class_id=? AND u.is_active=1
        GROUP BY u.id
        ORDER BY total_xp DESC
        LIMIT ?
    """, (class_id, limit)).fetchall()
    conn.close()
    return rows