import json
import random
from flask import Flask, render_template, request, \
    make_response
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config.from_pyfile('config.txt')
db = SQLAlchemy()


def f(func):
    def wrapper(*args, **kwargs):
        if request.cookies.get('username'):
            return func(*args, **kwargs)
        else:
            return '页面不见了'

    return wrapper


class Students(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(length=20))
    math = db.Column(db.Integer)
    chinese = db.Column(db.Integer)
    english = db.Column(db.Integer)


class Users(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(length=20), unique=True)
    password = db.Column(db.String(length=20))


db.init_app(app)


# 创建数据表
@app.cli.command()
def create():
    db.create_all()


# 添加数据
@app.cli.command()
def one():
    stu1 = Students(name='张七', math=99, english=25,
                    chinese=83)
    db.session.add(stu1)
    db.session.commit()


@app.route('/', methods=['GET', 'POST'], endpoint='hello')
def hello_world():
    if request.method == 'GET':
        return render_template('login.html')
    else:
        return {'message': 200}


# 返回数据

@app.route('/info', endpoint='info')
@f
def info():
    page = request.args.get('page', default=1)
    page = int(page)
    limit = request.args.get('limit', default=10)
    limit = int(limit)
    name = request.args.get('key[name]')
    if not name:
        paginate = Students.query.paginate(page=page,
                                           per_page=limit)
    else:
        paginate = Students.query.filter(
            Students.name.like('%' + name + '%')).paginate(
            page=page, per_page=limit)

    return {
        'code': 0,
        'count': paginate.total,
        'data': [{'id': student.id, 'name': student.name,
                  'math': student.math,
                  'english': student.english,
                  'chinese': student.chinese} for student in
                 paginate.items]
    }


# 删除信息
@app.route('/delete', methods=['GET', 'POST'],
           endpoint='delete')
@f
def delete():
    if request.method == 'POST':
        # 将二进制数据转字典
        id = json.loads(request.data).get('id')
        stu = Students.query.get(id)
        db.session.delete(stu)
        db.session.commit()
    return {'message': 'success'}


# 添加测试数据
@app.cli.command()
def add():
    for i in range(20):
        stu = Students(name=f'stu{i}',
                       math=random.randint(1, 100),
                       english=random.randint(1, 100),
                       chinese=random.randint(1, 100))
        db.session.add(stu)
    db.session.commit()


# 表格信息编辑
@app.route('/edit', methods=['POST'], endpoint='edit')
@f
def edit():
    data = json.loads(request.data)
    id = data.get('id')
    name = data.get('name')
    math = data.get('math')
    chinese = data.get('chinese')
    english = data.get('english')
    stu = Students.query.get(id)
    stu.name = name
    stu.math = math
    stu.chinese = chinese
    stu.english = english
    db.session.commit()
    return {'status': 200}


@app.route('/table', endpoint='table')
@f
def table():
    return render_template('index.html')


# 后台中心

@app.route('/admin', methods=['GET'], endpoint='admin')
@f
def admin():
    response = make_response(render_template('admin.html'))
    return response


# 添加学生信息
@app.route('/add', methods=['POST'], endpoint='add')
@f
def add():
    data = json.loads(request.data)
    name = data.get('name')
    math = data.get('math')
    chinese = data.get('chinese')
    english = data.get('english')
    stu = Students(name=name, math=math, chinese=chinese,
                   english=english)
    db.session.add(stu)
    db.session.commit()
    return {'status': 200}


# 可视化
@app.route('/graph', endpoint='graph')
@f
def graph():
    stus = Students.query.all()
    avg_math = sum([i.math for i in stus]) / len(stus)
    avg_english = sum([i.english for i in stus]) / len(stus)
    avg_chinese = sum([i.chinese for i in stus]) / len(stus)
    total_math = [0, 0, 0, 0]
    total_english = [0, 0, 0, 0]
    total_chinese = [0, 0, 0, 0]
    for i in stus:
        if i.math >= 0 and i.math <= 60:
            total_math[0] += 1
        if i.math > 60 and i.math <= 70:
            total_math[1] += 1
        if i.math > 70 and i.math < 90:
            total_math[2] += 1
        if i.math >= 90:
            total_math[3] += 1
        if i.english >= 0 and i.english <= 60:
            total_english[0] += 1
        if i.english > 60 and i.english <= 70:
            total_english[1] += 1
        if i.english > 70 and i.english < 90:
            total_english[2] += 1
        if i.english >= 90:
            total_english[3] += 1
        if i.chinese >= 0 and i.chinese <= 60:
            total_chinese[0] += 1
        if i.chinese > 60 and i.chinese <= 70:
            total_chinese[1] += 1
        if i.chinese > 70 and i.chinese < 90:
            total_chinese[2] += 1
        if i.chinese >= 90:
            total_chinese[3] += 1

    return render_template('graph.html',
                           avg_chinese=avg_chinese,
                           avg_english=avg_english,
                           avg_math=avg_math
                           , total_math=total_math,
                           total_chinese=total_chinese,
                           total_english=total_english)


# check
@app.route('/sign', methods=['POST'])
def sign():
    data = json.loads(request.data)
    username = data.get('username')
    password = data.get('password')
    res = make_response({'message': 'sign'})
    try:
        user = Users(username=username, password=password)
        db.session.add(user)
        db.session.commit()
    except Exception as e:
        print(e)
        res.status = '404'
    return res
#login
@app.route('/login',methods=['POST'])
def login():
    data = json.loads(request.data)
    username = data.get('username')
    password = data.get('password')
    print(username,password)
    user = Users.query.filter(Users.username==username,Users.password==password).first()
    if user:
        res = make_response({'message':'200'})
        res.set_cookie('username',username)
    else:
        res = make_response({'message':'404'})
        res.status = '404'
    return res

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