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分组, 再求平均值"""
    # start_es = convert_es_str(start)
    # end_es = convert_es_str(end)
    # 所有报警类型
    query_body = {
        "query": {
            "bool": {
                "filter": [
                    {
                        "terms": {
                            "pid": point_list
                        }
                    },
                    {
                        "range": {
                            "quarter_time": {
                                "gte": start,
                                "lte": end
                            }
                        }
                    }
                ]
            }
        },
        "size": 0,
        "aggs": {
            "points": {
                "terms": {
                    "field": "pid",
                    "size": 1000
                },
                "aggs": {
                    "ua_mean_avg": {
                        "avg": {
                            "field": "ua_mean"
                        }
                    },
                    "freq_mean_avg": {
                        "avg": {
                            "field": "freq_mean"
                        }
                    },
                    "ubl_mean_avg": {
                        "avg": {
                            "field": "ubl_mean"
                        }
                    },
                    "costtl_mean_avg": {
                        "avg": {
                            "field": "costtl_mean"
                        }
                    },
                    "thdua_mean_avg": {
                        "avg": {
                            "field": "thdua_mean"
                        }
                    },
                    "lf_mean_avg": {
                        "avg": {
                            "field": "lf_mean"
                        }
                    },
                    "uab_mean_avg": {
                        "avg": {
                            "field": "ua_mean"
                        }
                    }
                }
            }
        }
    }
    log.info("query_body={}".format(query_body))
    async with EsUtil() as es:
        es_result = await es.search_origin(body=query_body,
                                           index=constants.POINT_15MIN_INDEX)
    return es_result["aggregations"]["points"]["buckets"]
