import json
import os
from pymysql import *
import pymysql
import time

from flask import Flask, request, render_template, session, redirect, jsonify
import re

from werkzeug.utils import secure_filename

from utils.query import querys
from utils.data_acquisition import *

import random

app = Flask(__name__)
app.secret_key = 'This is a app.secret_Key , You Know ?'
app.config['UPLOAD_FOLDER'] = 'D:/glmwork'
app.config['ALLOWED_EXTENSIONS'] = {'txt', 'csv', 'xlsx', 'xls', 'json', 'sql'}


conn = connect(host='localhost',user='root',password='root',database='games',port=3306)
cursor = conn.cursor()

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


@app.route("/home")
def home():
    email = session['email']

    return render_template(
        "index.html",
        email=email
    )


@app.route("/login", methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        request.form = dict(request.form)

        def filter_fns(item):
            return request.form['email'] in item and request.form['password'] in item

        users = querys('select * from users', [], 'select')
        login_success = list(filter(filter_fns, users))
        if not len(login_success):
            return '账号或密码错误'

        session['email'] = request.form['email']
        return redirect('/home', 301)

    else:
        
        return render_template('./login.html')


@app.route("/registry", methods=['GET', 'POST'])
def registry():
    if request.method == 'POST':
        request.form = dict(request.form)
        if request.form['password'] != request.form['passwordCheked']:
            return '两次密码不符'
        else:
            def filter_fn(item):
                return request.form['email'] in item

            users = querys('select * from users', [], 'select')
            filter_list = list(filter(filter_fn, users))
            if len(filter_list):
                return '该用户名已被注册'
            else:
                querys('insert into users(Email,Password,UserName) values(%s,%s,%s)',
                       [request.form['email'], request.form['password'],request.form['userName']])

        session['email'] = request.form['email']
        return redirect('/home', 301)

    else:
        return render_template('./register.html')


@app.route("/task_submit", methods=['GET', 'POST'])
def task_submit():
    email = session['email']

    return render_template(
        'task_submit.html',
        email=email
    )

@app.route("/game_submit", methods=['GET', 'POST'])
def game_submit():
    email = session['email']

    return render_template(
        'game_submit.html',
        email=email
    )


def allowed_file(filename):
    return '.' in filename and \
        filename.rsplit('.', 1)[1].lower() in app.config['ALLOWED_EXTENSIONS']


@app.route('/submit_task', methods=['POST'])
def submit_task():
    try:
        email = session['email']
        
        UserID = querys('SELECT UserID FROM users WHERE Email = %s', (email,), 'select')
        UserID = UserID[0][0]
        print(UserID)
        
        name = request.form['task_name']
        score = request.form['task_score']
        description = request.form['task_description']
        
        # 从Games表中获取GameID
        query_get_game_id = 'SELECT GameID FROM games WHERE GameName = %s'
        cursor.execute(query_get_game_id, name)
        game_id_result = cursor.fetchone()
        
        if game_id_result:
            game_id = game_id_result[0]
            print(game_id)
            
            query.querys('insert into ratings(UserID,GameID,Score,Comment) values(%s,%s,%s,%s)', (UserID,game_id,score,description), 'insert')
        else:
            print("game not found")

        return jsonify({"message": "评分已提交"}), 200
    except Exception as e:
        print(e)
        return jsonify({"error": "评分提交失败"}), 500



@app.route('/submit_game', methods=['POST'])
def submit_game():
    try:
        name = request.form['taskName']
        publisher = request.form['publisher']
        description = request.form['taskDescription']
        genre = request.form['taskGenre']
        platform = request.form['platform']
        r0 = 0
    
        query.querys('insert into games(GameName,Publisher,Platform,Genre,Rating) values(%s,%s,%s,%s,%s)', (name,publisher,platform,genre,r0), 'insert')
        
        time.sleep(3)
        
        # 从Games表中获取GameID
        query_get_game_id = 'SELECT GameID FROM games WHERE GameName = %s'
        cursor.execute(query_get_game_id, name)
        game_id_result = cursor.fetchone()
        game_id = game_id_result[0]
        
        print(game_id,publisher,description)
        
        query.querys('insert into gamedetails(GameID,Developer,Description) values(%s,%s,%s)', (game_id,publisher,description), 'insert')
        
        return jsonify({"message": "游戏已提交"}), 200
    
    except Exception as e:
        print(e)
        return jsonify({"error": "游戏提交失败"}), 500
                     
@app.route("/single_community/<task_number>", methods=['GET', 'POST'])
def single_community(task_number):
    email = session['email']
    ratingID = getRatings(task_number)
    ratingID = ratingID.tolist()
    score = getScores(task_number)
    score = score.tolist()
    
    game_description = querys('SELECT Description FROM gamedetails WHERE GameID = %s', (task_number,), 'select')
    game_name = querys('SELECT GameName FROM games WHERE GameID = %s', (task_number,), 'select')
    
    # 获取评分区间的个数数据
    ratings_data = querys('SELECT FLOOR(Score) AS scoreRange, COUNT(*) AS count FROM ratings WHERE GameID = %s GROUP BY FLOOR(Score)', (task_number,), 'select')
    print(ratings_data)
    
    # 构造评分分布数据
    score_distribution = []
    for rating in ratings_data:
        score_range = f"{rating[0]}-{rating[0] + 0.9}"
        count = rating[1]
        #此处还有问题
        score_distribution.append({"scoreRange": score_range, "count": count})
    
    # 获取评分和评论
    ratings = querys('SELECT Score, Comment FROM ratings WHERE GameID = %s', (task_number,), 'select')

    
    return render_template(
        'single_community.html',
        task_number=task_number,
        email=email,
        ratingID = ratingID,
        score = score,
        game_description = game_description[0][0],
        game_name = game_name[0][0],
        ratings = ratings,
        ratings_data = score_distribution
    )

@app.route("/community_info/<int:community_id>", methods=['GET', 'POST'])
def community_info(community_id):
    email = session['email']
    
    UserID = querys('SELECT UserID FROM users WHERE Email = %s', (email,), 'select')
    UserID = UserID[0][0]
    print(UserID)
    
    community_description = querys('SELECT Description FROM communities WHERE CommunityID = %s', (community_id,), 'select')
    community_name = querys('SELECT CommunityName FROM communities WHERE CommunityID = %s', (community_id,), 'select')
    
    if request.method == 'POST':
        # 检查用户是否已经加入该社区
        already_in_community = querys(
            'SELECT COUNT(*) FROM player_community WHERE PlayerID = %s',
            (UserID,), 'select'
        )[0][0]  # 获取查询结果的第一个元素
        
        
        if already_in_community > 0:
            return jsonify({'error': '您已加入过社区'}), 400
            
        # 处理加入社区的逻辑
        try:
            querys('INSERT INTO player_community (PlayerID, CommunityID) VALUES (%s, %s)', (UserID, community_id, ), 'insert')
            print("ok")
            return jsonify({'message': '您已成功加入社区'}), 200
        except Exception as e:
            return jsonify({'error': str(e)}), 500
    
    
    community_members = querys('SELECT PlayerID FROM player_community WHERE CommunityID = %s', (community_id,), 'select')
    comments = []
    for member in community_members:
        member_id = member[0]
        member_comments = querys('SELECT GameName, Comment, Score FROM ratings JOIN games ON ratings.GameID = games.GameID WHERE UserID = %s', (member_id,), 'select')
        comments.extend(member_comments)
    
    #社区喜好游戏板块
    query = """
    SELECT games.GameName, AVG(ratings.Score) as averageScore
    FROM games
    JOIN ratings ON games.GameID = ratings.GameID
    JOIN users ON ratings.UserID = users.UserID
    JOIN player_community ON users.UserID = player_community.PlayerID
    WHERE player_community.CommunityID = %s
    GROUP BY games.GameID
    ORDER BY averageScore DESC;
    """
    cursor.execute(query, (community_id,))
    game_ratings_data = cursor.fetchall()
    
    # 构造数据
    game_ratings_list = [
        {'gameName': row[0], 'averageScore': row[1]} for row in game_ratings_data
    ]
    
    #用户评论信息板块
    query = """
    SELECT u.UserName, u.UserID, COUNT(r.GameID) as comment_count
    FROM users u
    JOIN player_community pc ON u.UserID = pc.PlayerID
    LEFT JOIN ratings r ON u.UserID = r.UserID
    WHERE pc.CommunityID = %s
    GROUP BY u.UserID;
    """
    cursor.execute(query, (community_id,))
    members_data = cursor.fetchall()
    
    # 构造数据
    members_list = [
        {'UserName': row[0], 'userID': row[1], 'commentCount': row[2]} for row in members_data
    ]
    print(members_list)
    
    return render_template(
        'community_info.html',
        email=email,
        community_description = community_description[0][0],
        community_name = community_name[0][0],
        user_id = UserID,
        community_id = community_id,
        comments = comments,
        game_ratings_list = game_ratings_list,
        members_list = members_list
    )

#查看游戏总评分界面
@app.route('/load_history_tasks', methods=['GET'])
def load_history_tasks():
    page = int(request.args.get('page', 1))  # 获取页码
    size = int(request.args.get('size', 10))  # 获取每页显示的条目数
    
    # 查询总记录数
    total_query = "SELECT COUNT(*) FROM games"
    cursor.execute(total_query)
    total_count = cursor.fetchone()[0]
    print(total_count)
    
    # 查询当前页的数据
    offset = (page - 1) * size
    query = "SELECT * FROM games LIMIT %s OFFSET %s"
    cursor.execute(query, (size, offset))
    rows = cursor.fetchall()
    
    # 构造任务列表
    tasks = [
        {"gameID": row[0], "game_name": row[1], "publisher": row[2], "platform": row[3], "genre": row[4], "rating": row[5],
         "url": "/single_community/" + str(row[0])}  # game_id 是 row[0]
        for row in rows
    ]
    
    #print(tasks)
    print((total_count + size - 1) // size)
    
    # 返回数据和总页数
    return jsonify({
        'tasks': tasks,
        'totalPages': (total_count + size - 1) // size  # 计算总页数
    })
    
#查看社区界面
@app.route('/load_history_communities', methods=['GET'])
def load_history_communities():
    page = int(request.args.get('page', 1))  # 获取页码
    size = int(request.args.get('size', 10))  # 获取每页显示的条目数
    
    # 查询总记录数
    total_query = "SELECT COUNT(*) FROM communities"
    cursor.execute(total_query)
    total_count = cursor.fetchone()[0]
    print(total_count)
    
    # 查询当前页的数据
    offset = (page - 1) * size
    query = "SELECT * FROM communities LIMIT %s OFFSET %s"
    cursor.execute(query, (size, offset))
    rows = cursor.fetchall()
    
    # 构造任务列表
    communities = [
        {"CommunityID": row[0], "CommunityName": row[1], "Description": row[2], "CreationDate": row[3],
         "url": "/community_info/" + str(row[0])}  # community_id 是 row[0]
        for row in rows
    ]
    
    #print(tasks)
    print((total_count + size - 1) // size)
    
    # 返回数据和总页数
    return jsonify({
        'communities': communities,
        'totalPages': (total_count + size - 1) // size  # 计算总页数
    })

@app.route('/task_list')
def task_list():
    email = session['email']

    return render_template('task_list.html', email=email)

@app.route('/community_list')
def community_list():
    email = session['email']

    return render_template('community_list.html', email=email)

@app.before_request
def before_requre():
    pat = re.compile(r'^/static')
    if re.search(pat, request.path):
        return
    if request.path == "/login":
        return
    if request.path == '/registry':
        return
    uname = session.get('email')
    if uname:
        return None

    return redirect("/login")


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