app.py

import os
import sqlite3
import io
import docx
import pandas as pd
from datetime import datetime, date
from flask import Flask, render_template, request, redirect, url_for, session, flash, send_file, jsonify
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename

app = Flask(__name__)
app.secret_key = 'super_secret_key_for_e_learning_ceria'

UPLOAD_FOLDER = os.path.join(os.path.dirname(__file__), 'static', 'uploads')
os.makedirs(UPLOAD_FOLDER, exist_ok=True)
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

DB_PATH = os.path.join(os.path.dirname(__file__), 'elearning.db')

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

def init_db():
    conn = get_db_connection()
    conn.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            role TEXT NOT NULL DEFAULT 'murid',
            points INTEGER DEFAULT 0,
            biodata TEXT DEFAULT '',
            profile_pic TEXT DEFAULT ''
        )
    ''')
    conn.execute('''
        CREATE TABLE IF NOT EXISTS assignments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT,
            created_by INTEGER
        )
    ''')
    
    conn.execute('''
        CREATE TABLE IF NOT EXISTS exams (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT,
            duration INTEGER,
            created_by INTEGER
        )
    ''')
    
    conn.execute('''
        CREATE TABLE IF NOT EXISTS attendance (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            date TEXT NOT NULL,
            status TEXT DEFAULT 'Hadir'
        )
    ''')
    
    conn.execute('''
        CREATE TABLE IF NOT EXISTS exam_participants (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            exam_id INTEGER,
            user_id INTEGER,
            is_cheating BOOLEAN DEFAULT 0,
            is_kicked BOOLEAN DEFAULT 0,
            score INTEGER DEFAULT 0
        )
    ''')
    
    conn.execute('''
        CREATE TABLE IF NOT EXISTS questions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            exam_id INTEGER,
            question_text TEXT NOT NULL,
            image_filename TEXT DEFAULT '',
            option_a TEXT,
            option_b TEXT,
            option_c TEXT,
            option_d TEXT,
            correct_option TEXT
        )
    ''')
    
    conn.execute('''
        CREATE TABLE IF NOT EXISTS student_answers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            participant_id INTEGER,
            question_id INTEGER,
            selected_option TEXT,
            is_correct BOOLEAN DEFAULT 0
        )
    ''')
    
    try:
        conn.execute('ALTER TABLE users ADD COLUMN biodata TEXT DEFAULT ""')
    except sqlite3.OperationalError:
        pass
        
    try:
        conn.execute('ALTER TABLE users ADD COLUMN profile_pic TEXT DEFAULT ""')
    except sqlite3.OperationalError:
        pass
        
    try:
        conn.execute('ALTER TABLE assignments ADD COLUMN image_filename TEXT DEFAULT ""')
    except sqlite3.OperationalError:
        pass
        
    try:
        conn.execute('ALTER TABLE exams ADD COLUMN image_filename TEXT DEFAULT ""')
    except sqlite3.OperationalError:
        pass
        
    try:
        conn.execute('ALTER TABLE exam_participants ADD COLUMN start_time TEXT DEFAULT ""')
        conn.execute('ALTER TABLE exam_participants ADD COLUMN end_time TEXT DEFAULT ""')
    except sqlite3.OperationalError:
        pass
        
    try:
        conn.execute('ALTER TABLE users ADD COLUMN last_seen TEXT DEFAULT ""')
    except sqlite3.OperationalError:
        pass
        
    try:
        conn.execute('ALTER TABLE assignments ADD COLUMN target_area TEXT DEFAULT "semua"')
    except sqlite3.OperationalError:
        pass

    try:
        conn.execute('ALTER TABLE exams ADD COLUMN target_area TEXT DEFAULT "semua"')
    except sqlite3.OperationalError:
        pass
    
    # Insert default admin if not exists
    admin_exists = conn.execute("SELECT * FROM users WHERE username = 'admin'").fetchone()
    if not admin_exists:
        conn.execute('INSERT INTO users (username, password, role) VALUES (?, ?, ?)',
                     ('admin', generate_password_hash('admin123'), 'admin'))
                     
    # Insert default pengajar (guru) if not exists
    guru_exists = conn.execute("SELECT * FROM users WHERE username = 'guru'").fetchone()
    if not guru_exists:
        conn.execute('INSERT INTO users (username, password, role) VALUES (?, ?, ?)',
                     ('guru', generate_password_hash('guru123'), 'pengajar'))

    conn.commit()
    conn.close()

# Initialize Database
init_db()

@app.before_request
def update_last_seen():
    if 'user_id' in session:
        try:
            conn = get_db_connection()
            conn.execute('UPDATE users SET last_seen = ? WHERE id = ?', (datetime.now().strftime('%Y-%m-%d %H:%M:%S'), session['user_id']))
            conn.commit()
            conn.close()
        except Exception:
            pass

@app.route('/')
def index():
    if 'user_id' in session:
        role = session.get('role')
        if role == 'admin':
            return redirect(url_for('admin_dashboard'))
        elif role == 'pengajar':
            return redirect(url_for('pengajar_dashboard'))
        else:
            return redirect(url_for('dashboard'))
    return render_template('login.html')

@app.route('/login', methods=['POST'])
def login():
    username = request.form['username']
    password = request.form['password']
    
    conn = get_db_connection()
    user = conn.execute('SELECT * FROM users WHERE username = ?', (username,)).fetchone()
    conn.close()
    
    if user and check_password_hash(user['password'], password):
        session['user_id'] = user['id']
        session['username'] = user['username']
        session['role'] = user['role']
        session['points'] = user['points']
        
        if user['role'] == 'admin':
            return redirect(url_for('admin_dashboard'))
        elif user['role'] == 'pengajar':
            return redirect(url_for('pengajar_dashboard'))
        else:
            return redirect(url_for('dashboard'))
    else:
        flash('Username atau Password salah!', 'error')
        return redirect(url_for('index'))

@app.route('/admin_login', methods=['POST'])
def admin_login():
    username = request.form['username']
    password = request.form['password']
    
    conn = get_db_connection()
    user = conn.execute('SELECT * FROM users WHERE username = ? AND role = ?', (username, 'admin')).fetchone()
    conn.close()
    
    if user and check_password_hash(user['password'], password):
        session['user_id'] = user['id']
        session['username'] = user['username']
        session['role'] = user['role']
        session['points'] = user['points']
        return redirect(url_for('admin_dashboard'))
    else:
        flash('Akses ditolak atau Password Admin salah!', 'error')
        return redirect(url_for('index'))

@app.route('/register', methods=['POST'])
def register():
    username = request.form['username']
    password = request.form['password']
    role = 'murid' # Semua akun yang didaftarkan lewat form register otomatis jadi murid
    
    hashed_password = generate_password_hash(password)
    
    conn = get_db_connection()
    try:
        conn.execute('INSERT INTO users (username, password, role) VALUES (?, ?, ?)',
                     (username, hashed_password, role))
        conn.commit()
        flash('Pendaftaran berhasil! Silakan login.', 'success')
    except sqlite3.IntegrityError:
        flash('Username sudah digunakan.', 'error')
    finally:
        conn.close()
    
    return redirect(url_for('index'))

@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('index'))

@app.route('/dashboard')
def dashboard():
    if session.get('role') != 'murid':
        return redirect(url_for('index'))
    
    conn = get_db_connection()
    assignments = conn.execute('SELECT * FROM assignments ORDER BY id DESC').fetchall()
    exams = conn.execute('SELECT * FROM exams ORDER BY id DESC').fetchall()
    conn.close()
    
    tk_assignments = [a for a in assignments if a['target_area'] in ['tk', 'semua']]
    sd_assignments = [a for a in assignments if a['target_area'] in ['sd', 'semua']]
    tk_exams = [e for e in exams if e['target_area'] in ['tk', 'semua']]
    sd_exams = [e for e in exams if e['target_area'] in ['sd', 'semua']]
    
    return render_template('dashboard.html', username=session['username'], points=session.get('points', 0), 
                           tk_assignments=tk_assignments, sd_assignments=sd_assignments, 
                           tk_exams=tk_exams, sd_exams=sd_exams)

@app.route('/pengajar_dashboard')
def pengajar_dashboard():
    if session.get('role') != 'pengajar':
        return redirect(url_for('index'))
    
    conn = get_db_connection()
    students = conn.execute('SELECT username, points FROM users WHERE role = "murid" ORDER BY points DESC').fetchall()
    assignments = conn.execute('SELECT * FROM assignments ORDER BY id DESC').fetchall()
    exams = conn.execute('SELECT * FROM exams ORDER BY id DESC').fetchall()
    
    # Absensi hari ini
    today = date.today().strftime('%Y-%m-%d')
    attendance = conn.execute('''
        SELECT users.username, attendance.date, attendance.status 
        FROM attendance 
        JOIN users ON attendance.user_id = users.id 
        WHERE attendance.date LIKE ?
        ORDER BY attendance.date DESC
    ''', (today + '%',)).fetchall()
    
    # Online users
    all_users_raw = conn.execute('SELECT username, role, last_seen FROM users ORDER BY last_seen DESC').fetchall()
    now = datetime.now()
    online_usernames = set()
    for u in all_users_raw:
        if u['last_seen']:
            try:
                ls = datetime.strptime(u['last_seen'], '%Y-%m-%d %H:%M:%S')
                if (now - ls).total_seconds() < 300:
                    online_usernames.add(u['username'])
            except Exception:
                pass
    
    conn.close()
    
    return render_template('pengajar_dashboard.html', username=session['username'], students=students, assignments=assignments, exams=exams, attendance=attendance, all_users=all_users_raw, online_usernames=online_usernames)

@app.route('/admin_dashboard')
def admin_dashboard():
    if session.get('role') != 'admin':
        return redirect(url_for('index'))
        
    conn = get_db_connection()
    users = conn.execute('SELECT id, username, role, points, last_seen FROM users ORDER BY id ASC').fetchall()
    
    # All attendance
    attendance = conn.execute('''
        SELECT users.username, attendance.date, attendance.status 
        FROM attendance 
        JOIN users ON attendance.user_id = users.id 
        ORDER BY attendance.date DESC
    ''').fetchall()
    
    # Live Exam Participants
    exam_participants = conn.execute('''
        SELECT ep.id, u.username, e.title as exam_title, ep.is_cheating, ep.is_kicked 
        FROM exam_participants ep
        JOIN users u ON ep.user_id = u.id
        JOIN exams e ON ep.exam_id = e.id
        ORDER BY ep.id DESC
    ''').fetchall()
    
    exams = conn.execute('SELECT * FROM exams ORDER BY id DESC').fetchall()
    assignments = conn.execute('SELECT * FROM assignments ORDER BY id DESC').fetchall()
    
    now = datetime.now()
    online_usernames = set()
    for u in users:
        if u['last_seen']:
            try:
                ls = datetime.strptime(u['last_seen'], '%Y-%m-%d %H:%M:%S')
                if (now - ls).total_seconds() < 300:
                    online_usernames.add(u['username'])
            except Exception:
                pass
    
    conn.close()
        
    return render_template('admin_dashboard.html', username=session['username'], users=users, attendance=attendance, exam_participants=exam_participants, exams=exams, assignments=assignments, online_usernames=online_usernames)

@app.route('/tk_area')
def tk_area():
    if session.get('role') != 'murid':
        return redirect(url_for('index'))
    return render_template('tk_area.html')

@app.route('/sd_area')
def sd_area():
    if session.get('role') != 'murid':
        return redirect(url_for('index'))
    return render_template('sd_area.html')

@app.route('/add_point', methods=['POST'])
def add_point():
    if session.get('role') != 'murid':
        return {'success': False, 'message': 'Unauthorized'}, 401
    
    user_id = session['user_id']
    points_to_add = int(request.json.get('points', 10))
    
    conn = get_db_connection()
    conn.execute('UPDATE users SET points = points + ? WHERE id = ?', (points_to_add, user_id))
    conn.commit()
    user = conn.execute('SELECT points FROM users WHERE id = ?', (user_id,)).fetchone()
    conn.close()
    
    session['points'] = user['points']
    return {'success': True, 'new_points': user['points']}

@app.route('/edit_user/<int:user_id>', methods=['POST'])
def edit_user(user_id):
    if session.get('role') != 'admin':
        return redirect(url_for('index'))
    
    role = request.form.get('role')
    points = request.form.get('points', type=int)
    
    conn = get_db_connection()
    conn.execute('UPDATE users SET role = ?, points = ? WHERE id = ?', (role, points, user_id))
    conn.commit()
    conn.close()
    
    flash('Pengguna berhasil diperbarui!', 'success')
    return redirect(url_for('admin_dashboard'))

@app.route('/delete_user/<int:user_id>', methods=['POST'])
def delete_user(user_id):
    if session.get('role') != 'admin':
        return redirect(url_for('index'))
        
    conn = get_db_connection()
    conn.execute('DELETE FROM users WHERE id = ?', (user_id,))
    conn.commit()
    conn.close()
    
    flash('Pengguna berhasil dihapus!', 'success')
    return redirect(url_for('admin_dashboard'))

@app.route('/profile', methods=['GET', 'POST'])
def profile():
    if 'user_id' not in session:
        return redirect(url_for('index'))
        
    user_id = session['user_id']
    conn = get_db_connection()
    
    if request.method == 'POST':
        new_username = request.form['username']
        new_password = request.form.get('password')
        biodata = request.form.get('biodata', '')
        
        # Handle file upload
        profile_pic_filename = None
        if 'profile_pic' in request.files:
            file = request.files['profile_pic']
            if file and file.filename != '':
                filename = secure_filename(file.filename)
                # Tambahkan user_id di awal nama file untuk mencegah konflik
                filename = f"user_{user_id}_{filename}"
                file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
                profile_pic_filename = filename
        
        try:
            # Query builder dasar
            query = 'UPDATE users SET username = ?, biodata = ?'
            params = [new_username, biodata]
            
            if new_password:
                query += ', password = ?'
                params.append(generate_password_hash(new_password))
                
            if profile_pic_filename:
                query += ', profile_pic = ?'
                params.append(profile_pic_filename)
                
            query += ' WHERE id = ?'
            params.append(user_id)
            
            conn.execute(query, params)
            conn.commit()
            session['username'] = new_username
            flash('Profil berhasil diperbarui!', 'success')
        except sqlite3.IntegrityError:
            flash('Username sudah digunakan, silakan pilih yang lain.', 'error')
            
    user = conn.execute('SELECT * FROM users WHERE id = ?', (user_id,)).fetchone()
    conn.close()
    
    return render_template('profile.html', user=user)

# =========================
# LMS & ANTI-CHEAT ROUTES
# =========================

@app.route('/mark_attendance', methods=['POST'])
def mark_attendance():
    if session.get('role') != 'murid':
        return redirect(url_for('index'))
    user_id = session['user_id']
    today = date.today().strftime('%Y-%m-%d')
    now_full = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    
    conn = get_db_connection()
    # Check if already attended today (match date part only)
    exists = conn.execute("SELECT id FROM attendance WHERE user_id = ? AND date LIKE ?", (user_id, today + '%')).fetchone()
    if not exists:
        conn.execute('INSERT INTO attendance (user_id, date) VALUES (?, ?)', (user_id, now_full))
        conn.commit()
        flash('Absensi hari ini berhasil dicatat!', 'success')
    else:
        flash('Anda sudah absen hari ini.', 'info')
    conn.close()
    return redirect(url_for('dashboard'))

@app.route('/create_assignment', methods=['POST'])
def create_assignment():
    if session.get('role') != 'pengajar':
        return redirect(url_for('index'))
    title = request.form['title']
    description = request.form['description']
    target_area = request.form.get('target_area', 'semua')
    created_by = session['user_id']
    
    image_filename = ''
    if 'image' in request.files:
        file = request.files['image']
        if file and file.filename != '':
            filename = secure_filename(file.filename)
            file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            image_filename = filename
    
    conn = get_db_connection()
    conn.execute('INSERT INTO assignments (title, description, created_by, image_filename, target_area) VALUES (?, ?, ?, ?, ?)', (title, description, created_by, image_filename, target_area))
    conn.commit()
    conn.close()
    flash('Tugas berhasil dibuat!', 'success')
    return redirect(url_for('pengajar_dashboard'))

@app.route('/create_exam', methods=['POST'])
def create_exam():
    if session.get('role') != 'pengajar':
        return redirect(url_for('index'))
    title = request.form['title']
    description = request.form['description']
    duration = request.form['duration']
    target_area = request.form.get('target_area', 'semua')
    created_by = session['user_id']
    
    image_filename = ''
    if 'image' in request.files:
        file = request.files['image']
        if file and file.filename != '':
            filename = secure_filename(file.filename)
            file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            image_filename = filename
    
    conn = get_db_connection()
    conn.execute('INSERT INTO exams (title, description, duration, created_by, image_filename, target_area) VALUES (?, ?, ?, ?, ?, ?)', (title, description, duration, created_by, image_filename, target_area))
    conn.commit()
    conn.close()
    flash('Ujian berhasil dibuat!', 'success')
    return redirect(url_for('pengajar_dashboard'))

@app.route('/exam_room/<int:exam_id>')
def exam_room(exam_id):
    if session.get('role') != 'murid':
        return redirect(url_for('index'))
    user_id = session['user_id']
    
    conn = get_db_connection()
    exam = conn.execute('SELECT * FROM exams WHERE id = ?', (exam_id,)).fetchone()
    
    # Check or create participant
    participant = conn.execute('SELECT * FROM exam_participants WHERE exam_id = ? AND user_id = ?', (exam_id, user_id)).fetchone()
    if not participant:
        start_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        conn.execute('INSERT INTO exam_participants (exam_id, user_id, start_time) VALUES (?, ?, ?)', (exam_id, user_id, start_time))
        conn.commit()
        participant = conn.execute('SELECT * FROM exam_participants WHERE exam_id = ? AND user_id = ?', (exam_id, user_id)).fetchone()
    
    if participant['end_time']:
        conn.close()
        return redirect(url_for('exam_review', exam_id=exam_id))
        
    questions = conn.execute('SELECT * FROM questions WHERE exam_id = ? ORDER BY id ASC', (exam_id,)).fetchall()
    conn.close()
    
    if participant['is_kicked']:
        flash('Anda telah didiskualifikasi dari ujian ini karena terindikasi curang!', 'error')
        return redirect(url_for('dashboard'))
        
    return render_template('exam_room.html', exam=exam, participant=participant, questions=questions)

@app.route('/api_exam_cheat/<int:exam_id>', methods=['POST'])
def api_exam_cheat(exam_id):
    if session.get('role') != 'murid':
        return {'success': False}, 401
    user_id = session['user_id']
    
    conn = get_db_connection()
    conn.execute('UPDATE exam_participants SET is_cheating = 1 WHERE exam_id = ? AND user_id = ?', (exam_id, user_id))
    conn.commit()
    conn.close()
    return {'success': True}

@app.route('/api_check_kicked/<int:exam_id>')
def api_check_kicked(exam_id):
    if session.get('role') != 'murid':
        return {'kicked': False}, 401
    user_id = session['user_id']
    
    conn = get_db_connection()
    participant = conn.execute('SELECT is_kicked FROM exam_participants WHERE exam_id = ? AND user_id = ?', (exam_id, user_id)).fetchone()
    conn.close()
    
    kicked = bool(participant['is_kicked']) if participant else False
    return {'kicked': kicked}

@app.route('/exam_kick/<int:participant_id>', methods=['POST'])
def exam_kick(participant_id):
    if session.get('role') != 'admin':
        return redirect(url_for('index'))
        
    conn = get_db_connection()
    conn.execute('UPDATE exam_participants SET is_kicked = 1 WHERE id = ?', (participant_id,))
    conn.commit()
    conn.close()
    flash('Murid berhasil didiskualifikasi dari ujian!', 'success')
    return redirect(url_for('admin_dashboard'))

@app.route('/upload_assignment_word', methods=['POST'])
def upload_assignment_word():
    if session.get('role') != 'pengajar':
        return redirect(url_for('index'))
    
    if 'file' not in request.files:
        flash('Tidak ada file yang diunggah.', 'error')
        return redirect(url_for('pengajar_dashboard'))
        
    file = request.files['file']
    if file.filename == '':
        flash('Tidak ada file yang dipilih.', 'error')
        return redirect(url_for('pengajar_dashboard'))
        
    if file and file.filename.endswith('.docx'):
        try:
            doc = docx.Document(file)
            if len(doc.paragraphs) > 0:
                title = doc.paragraphs[0].text
                description = '\n'.join([p.text for p in doc.paragraphs[1:]])
                target_area = request.form.get('target_area', 'semua')
                created_by = session['user_id']
                
                conn = get_db_connection()
                conn.execute('INSERT INTO assignments (title, description, created_by, target_area) VALUES (?, ?, ?, ?)', (title, description, created_by, target_area))
                conn.commit()
                conn.close()
                flash('Tugas berhasil diimport dari Word!', 'success')
            else:
                flash('Dokumen Word kosong.', 'error')
        except Exception as e:
            flash(f'Terjadi kesalahan saat memproses Word: {str(e)}', 'error')
    else:
        flash('Harap unggah file dengan format .docx', 'error')
        
    return redirect(url_for('pengajar_dashboard'))

@app.route('/upload_exam_word', methods=['POST'])
def upload_exam_word():
    if session.get('role') != 'pengajar':
        return redirect(url_for('index'))
        
    duration = request.form.get('duration', 60)
    
    if 'file' not in request.files:
        flash('Tidak ada file yang diunggah.', 'error')
        return redirect(url_for('pengajar_dashboard'))
        
    file = request.files['file']
    if file.filename == '':
        flash('Tidak ada file yang dipilih.', 'error')
        return redirect(url_for('pengajar_dashboard'))
        
    if file and file.filename.endswith('.docx'):
        try:
            doc = docx.Document(file)
            if len(doc.paragraphs) > 0:
                title = doc.paragraphs[0].text
                description = '\n'.join([p.text for p in doc.paragraphs[1:]])
                target_area = request.form.get('target_area', 'semua')
                created_by = session['user_id']
                
                conn = get_db_connection()
                conn.execute('INSERT INTO exams (title, description, duration, created_by, target_area) VALUES (?, ?, ?, ?, ?)', (title, description, duration, created_by, target_area))
                conn.commit()
                conn.close()
                flash('Ujian berhasil diimport dari Word!', 'success')
            else:
                flash('Dokumen Word kosong.', 'error')
        except Exception as e:
            flash(f'Terjadi kesalahan saat memproses Word: {str(e)}', 'error')
    else:
        flash('Harap unggah file dengan format .docx', 'error')
        
    return redirect(url_for('pengajar_dashboard'))

@app.route('/export_exam_scores/<int:exam_id>')
def export_exam_scores(exam_id):
    if session.get('role') not in ['admin', 'pengajar']:
        return redirect(url_for('index'))
        
    conn = get_db_connection()
    exam = conn.execute('SELECT title FROM exams WHERE id = ?', (exam_id,)).fetchone()
    if not exam:
        conn.close()
        flash('Ujian tidak ditemukan.', 'error')
        return redirect(url_for('index'))
        
    participants = conn.execute('''
        SELECT u.username as Nama_Murid, ep.score as Nilai, 
               CASE WHEN ep.is_cheating = 1 THEN "Ya" ELSE "Tidak" END as Terindikasi_Curang,
               CASE WHEN ep.is_kicked = 1 THEN "Ya" ELSE "Tidak" END as Didiskualifikasi
        FROM exam_participants ep
        JOIN users u ON ep.user_id = u.id
        WHERE ep.exam_id = ?
    ''', (exam_id,)).fetchall()
    conn.close()
    
    # Convert to list of dicts for pandas
    data = [dict(row) for row in participants]
    df = pd.DataFrame(data)
    
    # Write to Excel in memory
    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Nilai Ujian')
    
    output.seek(0)
    safe_title = secure_filename(exam['title'])
    return send_file(output, as_attachment=True, download_name=f'Nilai_{safe_title}.xlsx', mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

@app.route('/edit_exam/<int:exam_id>')
def edit_exam(exam_id):
    if session.get('role') != 'pengajar':
        return redirect(url_for('index'))
        
    conn = get_db_connection()
    exam = conn.execute('SELECT * FROM exams WHERE id = ?', (exam_id,)).fetchone()
    questions = conn.execute('SELECT * FROM questions WHERE exam_id = ?', (exam_id,)).fetchall()
    conn.close()
    
    return render_template('edit_exam.html', exam=exam, questions=questions)

@app.route('/add_question/<int:exam_id>', methods=['POST'])
def add_question(exam_id):
    if session.get('role') != 'pengajar':
        return redirect(url_for('index'))
        
    q_text = request.form['question_text']
    opt_a = request.form['option_a']
    opt_b = request.form['option_b']
    opt_c = request.form['option_c']
    opt_d = request.form['option_d']
    correct = request.form['correct_option']
    
    conn = get_db_connection()
    conn.execute('INSERT INTO questions (exam_id, question_text, option_a, option_b, option_c, option_d, correct_option) VALUES (?, ?, ?, ?, ?, ?, ?)',
                 (exam_id, q_text, opt_a, opt_b, opt_c, opt_d, correct))
    conn.commit()
    conn.close()
    flash('Soal berhasil ditambahkan!', 'success')
    return redirect(url_for('edit_exam', exam_id=exam_id))

@app.route('/submit_exam/<int:exam_id>', methods=['POST'])
def submit_exam(exam_id):
    if session.get('role') != 'murid':
        return redirect(url_for('index'))
    user_id = session['user_id']
    
    conn = get_db_connection()
    participant = conn.execute('SELECT * FROM exam_participants WHERE exam_id = ? AND user_id = ?', (exam_id, user_id)).fetchone()
    if not participant or participant['end_time']:
        conn.close()
        return redirect(url_for('dashboard'))
        
    questions = conn.execute('SELECT * FROM questions WHERE exam_id = ?', (exam_id,)).fetchall()
    
    correct_count = 0
    total_questions = len(questions)
    
    for q in questions:
        ans = request.form.get(f'q_{q["id"]}')
        is_correct = 1 if ans == q['correct_option'] else 0
        if is_correct:
            correct_count += 1
            
        conn.execute('INSERT INTO student_answers (participant_id, question_id, selected_option, is_correct) VALUES (?, ?, ?, ?)',
                     (participant['id'], q['id'], ans, is_correct))
                     
    score = int((correct_count / total_questions) * 100) if total_questions > 0 else 0
    
    end_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    
    conn.execute('UPDATE exam_participants SET score = ?, end_time = ? WHERE id = ?', (score, end_time, participant['id']))
    conn.commit()
    conn.close()
    
    return redirect(url_for('exam_review', exam_id=exam_id))

@app.route('/exam_review/<int:exam_id>')
def exam_review(exam_id):
    if session.get('role') != 'murid':
        return redirect(url_for('index'))
    user_id = session['user_id']
    
    conn = get_db_connection()
    exam = conn.execute('SELECT * FROM exams WHERE id = ?', (exam_id,)).fetchone()
    participant = conn.execute('SELECT * FROM exam_participants WHERE exam_id = ? AND user_id = ?', (exam_id, user_id)).fetchone()
    
    if not participant or not participant['end_time']:
        conn.close()
        return redirect(url_for('dashboard'))
        
    questions = conn.execute('SELECT * FROM questions WHERE exam_id = ?', (exam_id,)).fetchall()
    answers_raw = conn.execute('SELECT * FROM student_answers WHERE participant_id = ?', (participant['id'],)).fetchall()
    conn.close()
    
    answers = {a['question_id']: a for a in answers_raw}
    
    return render_template('exam_review.html', exam=exam, participant=participant, questions=questions, answers=answers)

# ============================
# FILE MANAGEMENT ROUTES
# ============================

@app.route('/delete_assignment_file/<int:assignment_id>', methods=['POST'])
def delete_assignment_file(assignment_id):
    if session.get('role') not in ['admin', 'pengajar']:
        return redirect(url_for('index'))
    conn = get_db_connection()
    a = conn.execute('SELECT image_filename FROM assignments WHERE id = ?', (assignment_id,)).fetchone()
    if a and a['image_filename']:
        filepath = os.path.join(app.config['UPLOAD_FOLDER'], a['image_filename'])
        if os.path.exists(filepath):
            os.remove(filepath)
        conn.execute('UPDATE assignments SET image_filename = "" WHERE id = ?', (assignment_id,))
        conn.commit()
        flash('File tugas berhasil dihapus!', 'success')
    conn.close()
    return redirect(request.referrer or url_for('pengajar_dashboard'))

@app.route('/update_assignment_file/<int:assignment_id>', methods=['POST'])
def update_assignment_file(assignment_id):
    if session.get('role') not in ['admin', 'pengajar']:
        return redirect(url_for('index'))
    if 'image' not in request.files or request.files['image'].filename == '':
        flash('Pilih file baru terlebih dahulu.', 'error')
        return redirect(request.referrer or url_for('pengajar_dashboard'))
    
    conn = get_db_connection()
    a = conn.execute('SELECT image_filename FROM assignments WHERE id = ?', (assignment_id,)).fetchone()
    # Delete old file
    if a and a['image_filename']:
        old_path = os.path.join(app.config['UPLOAD_FOLDER'], a['image_filename'])
        if os.path.exists(old_path):
            os.remove(old_path)
    # Save new file
    file = request.files['image']
    filename = secure_filename(file.filename)
    file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
    conn.execute('UPDATE assignments SET image_filename = ? WHERE id = ?', (filename, assignment_id))
    conn.commit()
    conn.close()
    flash('File tugas berhasil diganti!', 'success')
    return redirect(request.referrer or url_for('pengajar_dashboard'))

@app.route('/delete_exam_file/<int:exam_id>', methods=['POST'])
def delete_exam_file(exam_id):
    if session.get('role') not in ['admin', 'pengajar']:
        return redirect(url_for('index'))
    conn = get_db_connection()
    e = conn.execute('SELECT image_filename FROM exams WHERE id = ?', (exam_id,)).fetchone()
    if e and e['image_filename']:
        filepath = os.path.join(app.config['UPLOAD_FOLDER'], e['image_filename'])
        if os.path.exists(filepath):
            os.remove(filepath)
        conn.execute('UPDATE exams SET image_filename = "" WHERE id = ?', (exam_id,))
        conn.commit()
        flash('File ujian berhasil dihapus!', 'success')
    conn.close()
    return redirect(request.referrer or url_for('pengajar_dashboard'))

@app.route('/update_exam_file/<int:exam_id>', methods=['POST'])
def update_exam_file(exam_id):
    if session.get('role') not in ['admin', 'pengajar']:
        return redirect(url_for('index'))
    if 'image' not in request.files or request.files['image'].filename == '':
        flash('Pilih file baru terlebih dahulu.', 'error')
        return redirect(request.referrer or url_for('pengajar_dashboard'))
    
    conn = get_db_connection()
    e = conn.execute('SELECT image_filename FROM exams WHERE id = ?', (exam_id,)).fetchone()
    # Delete old file
    if e and e['image_filename']:
        old_path = os.path.join(app.config['UPLOAD_FOLDER'], e['image_filename'])
        if os.path.exists(old_path):
            os.remove(old_path)
    # Save new file
    file = request.files['image']
    filename = secure_filename(file.filename)
    file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
    conn.execute('UPDATE exams SET image_filename = ? WHERE id = ?', (filename, exam_id))
    conn.commit()
    conn.close()
    flash('File ujian berhasil diganti!', 'success')
    return redirect(request.referrer or url_for('pengajar_dashboard'))

@app.route('/delete_assignment/<int:assignment_id>', methods=['POST'])
def delete_assignment(assignment_id):
    if session.get('role') not in ['admin', 'pengajar']:
        return redirect(url_for('index'))
    conn = get_db_connection()
    a = conn.execute('SELECT image_filename FROM assignments WHERE id = ?', (assignment_id,)).fetchone()
    if a and a['image_filename']:
        filepath = os.path.join(app.config['UPLOAD_FOLDER'], a['image_filename'])
        if os.path.exists(filepath):
            os.remove(filepath)
    conn.execute('DELETE FROM assignments WHERE id = ?', (assignment_id,))
    conn.commit()
    conn.close()
    flash('Tugas berhasil dihapus!', 'success')
    return redirect(request.referrer or url_for('pengajar_dashboard'))

@app.route('/delete_exam/<int:exam_id>', methods=['POST'])
def delete_exam(exam_id):
    if session.get('role') not in ['admin', 'pengajar']:
        return redirect(url_for('index'))
    conn = get_db_connection()
    e = conn.execute('SELECT image_filename FROM exams WHERE id = ?', (exam_id,)).fetchone()
    if e and e['image_filename']:
        filepath = os.path.join(app.config['UPLOAD_FOLDER'], e['image_filename'])
        if os.path.exists(filepath):
            os.remove(filepath)
    conn.execute('DELETE FROM questions WHERE exam_id = ?', (exam_id,))
    conn.execute('DELETE FROM student_answers WHERE participant_id IN (SELECT id FROM exam_participants WHERE exam_id = ?)', (exam_id,))
    conn.execute('DELETE FROM exam_participants WHERE exam_id = ?', (exam_id,))
    conn.execute('DELETE FROM exams WHERE id = ?', (exam_id,))
    conn.commit()
    conn.close()
    flash('Ujian beserta seluruh soalnya berhasil dihapus!', 'success')
    return redirect(request.referrer or url_for('pengajar_dashboard'))


if __name__ == '__main__':
    app.run(debug=True, port=5000)