"""
main -

Convention over Configuration ---> 约定大于配置
templates ---> 模板页
static ---> 静态资源

Author: Hao
Date: 2021/9/1
"""
import io
import random
from urllib.parse import quote

import flask
import openpyxl
import pymysql
from flask import make_response, request, redirect
from flask_cors import CORS

from utils import get_connection

app = flask.Flask(__name__)
CORS(app)


@app.route('/')
def show_index():
    return redirect('/static/index.html')


@app.route('/api/fruits')
def get_fruits():
    conn = get_connection()
    try:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute('select frt_id as id, frt_name as name, frt_image as image, '
                           'frt_intro as intro, frt_favs as favs from tb_fruit')
            fruits = cursor.fetchall()
        result = {'code': 10000, 'fruits': fruits}
    except pymysql.MySQLError as err:
        print(err)
        result = {'code': 10001, 'fruits': []}
    finally:
        conn.close()
    return result


@app.route('/export')
def export_excel():
    wb = openpyxl.Workbook()
    sheet = wb.active
    sheet.append(('编号', '水果名称', '水果介绍', '点赞数'))
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute('select frt_id, frt_name, frt_intro, frt_favs from tb_fruit')
            row_data = cursor.fetchone()
            while row_data:
                sheet.append(row_data)
                row_data = cursor.fetchone()
    except pymysql.MySQLError as err:
        print(err)
    finally:
        conn.close()
    buffer = io.BytesIO()
    wb.save(buffer)
    resp = make_response(buffer.getvalue())
    resp.headers['content-type'] = 'application/vnd.ms-excel'
    filename = quote('水果清单.xlsx')
    resp.headers['content-disposition'] = f'attachment; filename="{filename}"'
    return resp


@app.route('/api/delete')
def delete_fruit():
    fruit_id = request.args.get('id')
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute('delete from tb_fruit where frt_id=%s', (fruit_id, ))
        conn.commit()
        hint = {'code': 10000, 'message': '操作成功'}
    except pymysql.MySQLError as err:
        print(err)
        hint = {'code': 10001, 'message': '操作失败'}
        conn.rollback()
    finally:
        conn.close()
    return hint


@app.route('/api/like')
def like_fruit():
    fruit_id = request.args.get('id')
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                'update tb_fruit set frt_favs=frt_favs+1 where frt_id=%s',
                (fruit_id, )
            )
        conn.commit()
        hint = {'code': 10000, 'message': '操作成功'}
    except pymysql.MySQLError as err:
        print(err)
        hint = {'code': 10001, 'message': '操作失败'}
        conn.rollback()
    finally:
        conn.close()
    return hint


@app.route('/api/favs')
def get_fruit_favs():
    conn = get_connection()
    try:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(
                'select frt_name as name, frt_favs as value from tb_fruit'
            )
            fruits = cursor.fetchall()
    except pymysql.MySQLError as err:
        print(err)
        fruits = []
    return {'data': fruits}


@app.route('/api/sales')
def get_sales_data():
    y1_data = [random.randrange(10, 51) for _ in range(6)]
    y2_data = [random.randrange(15, 46) for _ in range(6)]
    return {'y1': y1_data, 'y2': y2_data}


if __name__ == '__main__':
    app.run(host='localhost', port=8000, debug=True)