from pot_libs.es_util.es_utils import EsUtil
from pot_libs.logger import log
from unify_api import constants
from pot_libs.mysql_util.mysql_util import MysqlUtil


async def get_point_dats_dao(cid, pid):
    if pid:
        sql = f"SELECT pid,ctnum,vc FROM `point` where cid={cid} or pid={pid}"
    else:
        sql = f'SELECT pid,ctnum,vc FROM `point` where cid={cid}'
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
    return datas


async def get_mean_datas_dao(pids, words, start, end):
    if pids:
        # 为了统一词缀，方便后续取数据
        mid_sql = "pid, avg(%s) ua_mean, avg(%s) freq_mean, " \
                  "avg(%s) ubl_mean,avg(%s) costtl_mean,avg(%s) thdua_mean, " \
                  "avg(%s) lf_mean" % words
        sql = f"select {mid_sql} from point_15min_electric where pid in %s " \
              f"and create_time BETWEEN '{start}' and '{end}' GROUP BY pid"
        async with MysqlUtil() as conn:
            datas = await conn.fetchall(sql, args=(pids,))
        return datas
    return []


async def health_score_points_aggs(start, end, point_list):
    """根据points分组, 再求平均值"""

    sql = f"""
        SELECT
            pid,
            avg( uab_mean ) uab_mean_avg,
            avg( costtl_mean ) costtl_mean_avg,
            avg( lf_mean ) lf_mean_avg,
            avg( thduab_mean ) thduab_mean_avg,
            avg( ubl_mean ) ubl_mean_avg,
            avg( freq_mean ) freq_mean_avg,
            avg( ua_mean )  ua_mean_avg
        FROM
            point_15min_electric 
        WHERE
            create_time > "{start}" 
            AND create_time < "{end}" 
            AND pid in %s
        GROUP BY
            pid
        """
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(tuple(point_list),)) if point_list else []
    return datas
