import pandas as pd
from pot_libs.mysql_util.mysql_util import MysqlUtil


async def location_stats_statics(table_name, cid, start, end):
    sql = "SELECT mtid,lid,item,ad_type FROM location WHERE cid=%s"
    location_map = {}
    async with MysqlUtil() as conn:
        locations = await conn.fetchall(sql, args=(cid,))
    if not locations:
        return location_map
    for loca in locations:
        location_map[loca["lid"]] = loca
    datas_sql = f"SELECT * from {table_name} where lid in %s and create_time" \
                f" BETWEEN '{start}' and '{end}' order by create_time desc"
    lids = list(location_map.keys())
    async with MysqlUtil() as conn:
        results = await conn.fetchall(datas_sql, args=(lids,))
    if not results:
        return {}
    df = pd.DataFrame(list(results))
    for lid in lids:
        max_value = df.loc[df["lid"] == lid].value_max.max()
        if not pd.isna(max_value):
            max_datas = df.loc[
                df.loc[df["lid"] == lid].value_max.idxmax()].to_dict()
            max_value_time = max_datas.get("value_max_time")
            max_value_time = '' if pd.isnull(max_value_time) else str(
                max_value_time)
            max_value = round(max_value, 2)
        else:
            max_value, max_value_time = "", ""
        min_value = df.loc[df["lid"] == lid].value_min.min()
        if not pd.isna(min_value):
            min_datas = df.loc[
                df.loc[df["lid"] == lid].value_min.idxmin()].to_dict()
            min_value_time = min_datas.get("value_min_time")
            min_value_time = '' if pd.isnull(min_value_time) else str(
                min_value_time)
            min_value = round(min_value, 2)
        else:
            min_value, min_value_time = "", ""
        mean_value = df.loc[df["lid"] == lid].value_avg.mean()
        if not pd.isna(mean_value):
            mean_value = round(mean_value, 2) if mean_value else ""
        else:
            mean_value = ''
        location_map[lid].update({
            "max_value": max_value, "max_value_time": max_value_time,
            "min_value": min_value, "min_value_time": min_value_time,
            "mean_value": mean_value,
        })
    return location_map
