# 引入Flask模块、数据库sqlite
from flask import Flask, request, render_template, redirect, url_for, Response
import sqlite3
from flask import g
import pandas as pd
from io import BytesIO
# ...

def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect(
            'db.sqlite',
            detect_types=sqlite3.PARSE_DECLTYPES
        )
        g.db.row_factory = sqlite3.Row

    return g.db



conn = sqlite3.connect('db.sqlite')
cursor = conn.cursor()

# add this line to create user_ratings table
cursor.execute('CREATE TABLE IF NOT EXISTS user_ratings (username TEXT PRIMARY KEY)')

app = Flask(__name__)
# 初始化配置
app.config.update(
    # 设置密钥，用于表单CSRF保护
    SECRET_KEY='a random string',
    # 设置全部大写，防止SQL注入
    SQLALCHEMY_DATABASE_URI='sqlite:///db.sqlite'
)

# 将函数设为路由，展示给用户的页面
@app.route('/', methods=['GET', 'POST'])
def index():
    error = None
    if request.method == 'POST':
        username = request.form['username']
        score1 = request.form['score1']
        score2 = request.form['score2']
        score3 = request.form['score3']
        score4 = request.form['score4']
        score5 = request.form['score5']
        score6 = request.form['score6']
        score7 = request.form['score7']
        score8 = request.form['score8']
        score9 = request.form['score9']
        score10 = request.form['score10']
        score11 = request.form['score11']
        score12 = request.form['score12']
        score13 = request.form['score13']
        score14 = request.form['score14']
        score15 = request.form['score15']
        score16 = request.form['score16']
        score17 = request.form['score17']
        score18 = request.form['score18']
        score19 = request.form['score19']
        score20 = request.form['score20']
        try:
            conn = get_db()
            cursor = conn.cursor()
            cursor.execute('SELECT * FROM user_ratings WHERE username=:username', {'username': username})
            if cursor.fetchone() is not None:
                return "<script>alert('您已打分完毕，请勿重复打分！');window.location.href='/';</script>"
            cursor.execute('INSERT INTO scores (username, score1, score2, score3, score4, score5, score6, score7,'
                           'score8, score9, score10, score11, score12, score13, score14, score15, score16, score17,'
                            'score18, score19, score20) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
                           (username, score1, score2, score3,score4, score5, score6, score7,score8, score9, score10, score11,score12, score13, score14, score15,score16, score17, score18, score19, score20))
            cursor.execute('INSERT INTO user_ratings (username) VALUES (?)', (username,))
        except sqlite3.Error as e:
            error = '提交失败: ' + str(e)
        else:
            conn.commit()
            return redirect(url_for('thanks'))
    return render_template('index.html', error=error)


@app.route('/results')
def results():
    try:
        conn = get_db()
        cursor = conn.cursor()
        # 查询数据库，计算出3个选项的平均值
        cursor.execute('SELECT ROUND(AVG(score1),3), AVG(score2), AVG(score3), AVG(score4), AVG(score5), AVG(score6), '
                       'AVG(score7), AVG(score8), AVG(score9), AVG(score10), AVG(score11), AVG(score12), '
                       'AVG(score13), AVG(score14), AVG(score15), AVG(score16), AVG(score17), AVG(score18), AVG(score19), AVG(score20) FROM scores')
        row = cursor.fetchone()

        if not row:
            data = [0] * 20
        else:
            # 将查询结果转换为列表并四舍五入至小数点后3位
            data = [round(num, 3) if num is not None else None for num in row]
        # 提取分数排名前5的课题及其分数
        course_scores = [('课题{}'.format(i), score) for i, score in enumerate(data, start=1) if score is not None]
        top_courses = [(i, course, score) for i, (course, score) in enumerate(sorted(course_scores, key=lambda x: x[1], reverse=True)[:5], start=1)]

        return render_template('results.html', data=data, top_courses=top_courses)
    except Exception as e:
        return render_template('results.html', error=e)


@app.route('/thanks')
def thanks():
    return render_template('thanks.html')

app.config['WTF_CSRF_ENABLED'] = True
app.config['WTF_CSRF_METHODS'] = ['POST', 'PUT', 'PATCH', 'DELETE']
@app.route('/log')
def log():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM scores')
    rows = cursor.fetchall()
    return render_template('log.html', rows=rows)

@app.route('/delete/<username>')
def delete(username):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM scores WHERE username = ?', (username,))
    conn.commit()
    return redirect(url_for('log'))


@app.route('/my_route')
def my_route():
  return render_template('my_template.html', csrf_token=csrf_token())


@app.route('/download_excel')
def download_excel():
    # 从数据库获取数据
    conn = get_db()
    df = pd.read_sql_query("SELECT * from scores", conn)
    # 添加文件名参数和设置下载文件类型为 excel
    output = BytesIO()
    writer = pd.ExcelWriter(output, engine='openpyxl')
    df.to_excel(writer, index=False, sheet_name="评分记录")
    writer._save()
    output.seek(0)
    return Response(output, mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers={"Content-Disposition": "attachment;filename=score_records.xlsx"})


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)