from . import query
import json
import pandas as ps
import numpy as np
import re
from numpy import *
import pandas as ps

import csv
from collections import defaultdict
from datetime import datetime

def create_rating_list(num_ratings):
    communities = [f"社群{i + 1}" for i in range(20)]
    start_list = []
    for i in range(min(num_ratings, len(communities))):
        start_list.append({'name': communities[i], 'value': 0})
    return start_list


def getProportion(time,task_number):
    allData = getAllData3(task_number)
    propors = list()
    num = 0
    for i in allData:
        if i[0] == time:
            propors.append(i[2])
            num += 1
        if i[0] > time:
            break
    proporList = create_rating_list(num)
    for i in range(0, num):
        proporList[i]['value'] = float(propors[i])
        proporList[i]['name'] = "社群" + str(i)
    return proporList, time


def getSequence(number, task_number):
    allData = getAllData3(task_number)

    # 创建一个字典来存储每个月的节点数，键为年份和月份，值为节点数
    monthly_node_counts = defaultdict(int)
    monthly_clustere = defaultdict(float)
    monthly_modularization = defaultdict(float)
    monthly_centrality = defaultdict(float)

    # 假设CSV文件的日期格式是'YYYY-MM-DD'
    date_format = '%Y.%m.%d'
    for row in allData:
        # 检查社群标号是否匹配
        if row[1] == number:
            # 将日期字符串转换为datetime对象
            date_obj = datetime.strptime(row[0], date_format)
            # 提取年份和月份作为键
            month_key = (date_obj.year, date_obj.month, date_obj.day)
            # 更新该月份的节点数
            monthly_node_counts[month_key] += int(row[5])  # 假设node_count字段存储的是节点数的增量或总数
            monthly_clustere[month_key] += float(row[6])
            monthly_modularization[month_key] += float(row[7])
            monthly_centrality[month_key] += float(row[8])

    se_node = list()
    se_clu = list()
    se_modul = list()
    se_center = list()
    for month_key, node_count in sorted(monthly_node_counts.items()):
        se_node.append(node_count)
    for month_key, clu in sorted(monthly_clustere.items()):
        se_clu.append(clu)
    for month_key, modul in sorted(monthly_modularization.items()):
        se_modul.append(modul)
    for month_key, center in sorted(monthly_centrality.items()):
        se_center.append(center)

    return se_node, se_clu, se_modul, se_center

def getAllData(task_number):
    def map_fn(item):
        item=list(item)
        return item
    
    sql = "SELECT * FROM ratings where GameID = %s"
    allData = query.querys(sql, (task_number,), select)
    allData = list(map(map_fn, list(allData)))
    return allData


def getAllData2(task_number):
    def map_fn(item):
        item = list(item)
        return item
    
    allData = query.querys(sql, (), select)
    if task_number > '2':task_number='1'
    table_name = "csv1"+str(task_number)
    sql = "SELECT * FROM %s" % table_name
    allData = query.querys(sql, (), 'select')
    allData = list(map(map_fn, list(allData)))
    return allData


def getAllData3(task_number):
    def map_fn(item):
        item = list(item)
        return item

    if task_number > '2':task_number='1'
    table_name="csv2"+str(task_number)
    sql = "SELECT * FROM %s" % table_name
    allData = query.querys(sql, (), 'select')
    allData = list(map(map_fn, list(allData)))
    return allData

def getRatings(task_number):
    ratings = ps.DataFrame(getAllData(task_number),columns=[
        'RatingID',
        'UserID',
        'GameID',
        'Score',
        'Comment'
    ])['RatingID'].astype(int)
    return ratings

def getScores(task_number):
    ratings = ps.DataFrame(getAllData(task_number),columns=[
        'RatingID',
        'UserID',
        'GameID',
        'Score',
        'Comment'
    ])['Score'].astype(float)
    return ratings

def getComments(task_number):
    ratings = ps.DataFrame(getAllData(task_number),columns=[
        'RatingID',
        'UserID',
        'GameID',
        'Score',
        'Comment'
    ])['Comment']
    return ratings



def getTime(task_number):
    times = ps.DataFrame(getAllData2(task_number), columns=[
        '时间',
        '社群数量',
        '网络密度'
    ])['时间']
    return times


def getNum(task_number):
    Nums = ps.DataFrame(getAllData2(task_number), columns=[
        '时间',
        '社群数量',
        '网络密度'
    ])['社群数量'].astype(int)
    return Nums


def getDensity(task_number):
    dens = ps.DataFrame(getAllData2(task_number), columns=[
        '时间',
        '社群数量',
        '网络密度'
    ])['网络密度'].astype(float)
    return dens


def getlbs(cid1, cid2, task_number):
    if task_number > '2':task_number='1'
    table_name = "csv3" + str(task_number)
    sql = "select 时间,连边数 from %s where 社群标号1=%s and 社群标号2=%s" % (table_name, cid1, cid2)
    lbs = query.querys(sql, (), 'select')
    x = []
    y = []
    for i in lbs:
        x.append(i[0])
        y.append(i[1])
    return x, y


def gett12(cid1, cid2, task_number):
    if task_number > '2':task_number='1'
    table_name = "csv3" + str(task_number)
    sql = "select 时间,1流向2的人员数 from %s where 社群标号1=%s and 社群标号2=%s" % (table_name, cid1, cid2)
    t12 = query.querys(sql, (), 'select')
    x = []
    y = []
    for i in t12:
        x.append(i[0])
        y.append(i[1])
    return x, y


def gett21(cid1, cid2, task_number):
    if task_number > '2':task_number='1'
    table_name = "csv3" + str(task_number)
    sql = "select 时间,2流向1的人员数 from %s where 社群标号1=%s and 社群标号2=%s" % (table_name, cid1, cid2)
    t21 = query.querys(sql, (), 'select')
    x = []
    y = []
    for i in t21:
        x.append(i[0])
        y.append(i[1])
    return x, y


def getlc(cid, task_number):
    if task_number > '2':task_number='1'
    table_name = "csv2" + str(task_number)
    sql = "select 时间,立场 from %s where 社群标号=%s" % (table_name, cid)
    lc = query.querys(sql, (), 'select')
    x = []
    y = []
    for i in lc:
        x.append(i[0])
        y.append(i[1])
    return x, y


def zlc(y4, y5):
    y = []
    for i in y4:
        if i not in y:
            y.append(i)
    for i in y5:
        if i not in y:
            y.append(i)
    return y
