from flask import Flask, render_template, request, jsonify, send_file
import models
import pandas as pd
import os
from io import BytesIO

app = Flask(__name__)

@app.route('/')
def index():
    """主页"""
    return render_template('index.html')

@app.route('/random_pick')
def random_pick():
    """随机点名"""
    student = models.get_random_student()
    if student:
        return jsonify({
            'student_id': student[0],
            'name': student[1],
            'score': student[2],
            'call_count': student[3]
        })
    return jsonify({'error': '没有学生数据'})

@app.route('/sequential_pick')
def sequential_pick():
    """顺序点名"""
    student = models.get_sequential_student()
    if student:
        # 获取完整信息
        conn = models.sqlite3.connect('database.db')
        cursor = conn.cursor()
        cursor.execute('SELECT student_id, name, score, call_count FROM students WHERE student_id = ?', (student[0],))
        full_info = cursor.fetchone()
        conn.close()
        
        return jsonify({
            'student_id': full_info[0],
            'name': full_info[1],
            'score': full_info[2],
            'call_count': full_info[3]
        })
    return jsonify({'error': '没有学生数据'})

@app.route('/update_score', methods=['POST'])
def update_score():
    """更新积分"""
    data = request.json
    student_id = data.get('student_id')
    score_change = data.get('score_change', 0)
    
    models.update_score(student_id, score_change)
    return jsonify({'success': True})

@app.route('/import_excel', methods=['POST'])
def import_excel():
    """导入Excel文件"""
    if 'file' not in request.files:
        return jsonify({'error': '没有文件'})
    
    file = request.files['file']
    if file.filename == '':
        return jsonify({'error': '没有选择文件'})
    
    if file and file.filename.endswith('.xlsx'):
        # 保存文件
        file_path = 'temp_students.xlsx'
        file.save(file_path)
        
        # 导入数据库
        success = models.import_from_excel(file_path)
        
        # 删除临时文件
        os.remove(file_path)
        
        if success:
            return jsonify({'success': True, 'message': '导入成功'})
        else:
            return jsonify({'error': '导入失败，请检查文件格式'})
    
    return jsonify({'error': '请上传Excel文件(.xlsx)'})

@app.route('/export_data')
def export_data():
    """导出数据到Excel"""
    students = models.get_all_students()
    
    # 创建DataFrame
    df = pd.DataFrame(students, columns=['学号', '姓名', '积分', '点名次数'])
    
    # 创建Excel文件在内存中
    output = BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='点名数据', index=False)
    
    output.seek(0)
    
    return send_file(
        output,
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        as_attachment=True,
        download_name='点名数据导出.xlsx'
    )

@app.route('/get_ranking')
def get_ranking():
    """获取积分排名"""
    students = models.get_all_students()
    # 按积分排序
    sorted_students = sorted(students, key=lambda x: x[2], reverse=True)
    
    ranking_data = []
    for student in sorted_students[:10]:  # 前10名
        ranking_data.append({
            'name': student[1],
            'score': student[2],
            'call_count': student[3]
        })
    
    return jsonify(ranking_data)

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