from datetime import datetime

from pot_libs.es_util.es_utils import EsUtil
from pot_libs.logger import log
from pot_libs.mysql_util.mysql_util import MysqlUtil
from unify_api import constants
from unify_api.constants import SDU_ALARM_LIST
from unify_api.utils.time_format import proxy_power_slots


async def alarm_content_info(cid, start, end, points):
    """
    识电U报警统计
    """
    start_dt = datetime.strptime(start, "%Y-%m-%d %H:%M:%S")
    end_dt = datetime.strptime(end, "%Y-%m-%d %H:%M:%S")
    es_start_str = datetime(year=start_dt.year, month=start_dt.month,
                            day=start_dt.day).strftime(
        "%Y-%m-%dT%H:%M:%S+08:00")
    es_end_str = end_dt.strftime("%Y-%m-%dT%H:%M:%S+08:00")
    filter_list = [
        {"range": {"datetime": {"gte": es_start_str, "lte": es_end_str}}},
        {"term": {"cid": cid}}]
    query_body = {
        "size": 0,
        "query": {"bool": {"filter": filter_list}},
        "aggs": {
            "alarm_cnt": {
                "date_histogram": {
                    "field": "datetime",
                    "interval": "day",
                    "time_zone": "+08:00",
                    "format": "yyyy-MM-dd",
                    "min_doc_count": 0,
                    "extended_bounds": {"min": start_dt.strftime("%Y-%m-%d"),
                                        "max": end_dt.strftime("%Y-%m-%d")},
                },
                "aggs": {
                    "type_cnt": {
                        "terms": {"field": "type.keyword"}},
                    # 名字es和mysql可能会不一致, 使用point_id映射name
                    # "name_cnt": {
                    #     "terms": {"field": "name.keyword"}},
                    "point_cnt": {
                        "terms": {"field": "point_id"}},
                }
            },
        }
    }
    log.info("alarm_content_info query_body={}".format(query_body))
    async with EsUtil() as es:
        es_result = await es.search_origin(body=query_body,
                                           index=constants.POINT_1MIN_EVENT)
    buckets = es_result["aggregations"]["alarm_cnt"]["buckets"]

    high_power_app = {"slots": [], "value": [0] * len(buckets)}
    ele_overload = {"slots": [], "value": [0] * len(buckets)}
    illegal_ele_app = {"slots": [], "value": [0] * len(buckets)}
    power_quality = {"slots": [], "value": [0] * len(buckets)}
    ele_car_battery = {"slots": [], "value": [0] * len(buckets)}
    if not buckets:
        return None
    points_map = {i["pid"]: i["name"] for i in points}
    # 初始化,存储监测点,报警数量统计
    point_dic = {}
    for i in points:
        point_dic[i["name"]] = 0
    # 总报警数
    total_alarm_cnt = 0
    for index, bucket in enumerate(buckets):
        time_str = bucket["key_as_string"][5:10]
        high_power_app["slots"].append(time_str)
        ele_overload["slots"].append(time_str)
        illegal_ele_app["slots"].append(time_str)
        power_quality["slots"].append(time_str)
        ele_car_battery["slots"].append(time_str)
        # 1. 报警类型统计
        if bucket["type_cnt"]["buckets"]:
            for item in bucket["type_cnt"]["buckets"]:
                total_alarm_cnt += item["doc_count"]
                if item["key"] == "high_power_app":
                    high_power_app["value"][index] += item["doc_count"]
                if item["key"] == "illegal_ele_app":
                    illegal_ele_app["value"][index] += item["doc_count"]
                if item["key"] == "ele_overload":
                    ele_overload["value"][index] += item["doc_count"]
                if item["key"] == "power_quality_low":
                    power_quality["value"][index] += item["doc_count"]
                if item["key"] == "ele_car_battery":
                    ele_car_battery["value"][index] += item["doc_count"]

        # 2. 监测点,报警数量统计
        # if bucket["name_cnt"]["buckets"]:
        #     for item in bucket["name_cnt"]["buckets"]:
        #         point_dic[item["key"]] += item["doc_count"]
        if bucket["point_cnt"]["buckets"]:
            for item in bucket["point_cnt"]["buckets"]:
                point_id = item["key"]
                # 根据point_id map point_name
                name = points_map[point_id]
                point_dic[name] += item["doc_count"]

    log.info(f"high_power_app={high_power_app}")
    log.info(f"ele_overload={ele_overload}")
    log.info(f"illegal_ele_app={illegal_ele_app}")
    log.info(f"power_quality={power_quality}")
    log.info(f"ele_car_battery={ele_car_battery}")
    log.info(f"point_dic={point_dic}")
    log.info(f"total_alarm_cnt={total_alarm_cnt}")
    return {
        "high_power_app": high_power_app,
        "ele_overload": ele_overload,
        "illegal_ele_app": illegal_ele_app,
        "power_quality": power_quality,
        "ele_car_battery": ele_car_battery,
        "point_dic": point_dic,
        "total_alarm_cnt": total_alarm_cnt
    }


async def new_alarm_content_info(cids, start, end, points):
    """
    识电U正式版, 报警统计
    """
    start_dt = datetime.strptime(start, "%Y-%m-%d %H:%M:%S")
    end_dt = datetime.strptime(end, "%Y-%m-%d %H:%M:%S")
    es_start_str = datetime(year=start_dt.year, month=start_dt.month,
                            day=start_dt.day).strftime(
        "%Y-%m-%dT%H:%M:%S+08:00")
    es_end_str = end_dt.strftime("%Y-%m-%dT%H:%M:%S+08:00")
    filter_list = [
        {"range": {"datetime": {"gte": es_start_str, "lte": es_end_str}}},
        {"terms": {"cid": cids}},
        # 新版sdu, 限制报警类型
        {"terms": {"type.keyword": SDU_ALARM_LIST}}
    ]
    query_body = {
        "size": 0,
        "query": {"bool": {"filter": filter_list}},
        "aggs": {
            "alarm_cnt": {
                "date_histogram": {
                    "field": "datetime",
                    "interval": "day",
                    "time_zone": "+08:00",
                    "format": "yyyy-MM-dd",
                    "min_doc_count": 0,
                    "extended_bounds": {"min": start_dt.strftime("%Y-%m-%d"),
                                        "max": end_dt.strftime("%Y-%m-%d")},
                },
                "aggs": {
                    "type_cnt": {
                        "terms": {"field": "type.keyword"}},
                    # "name_cnt": {
                    #     "terms": {"field": "name.keyword"}},
                    # 用于计算报警户数
                    "point_cnt": {
                        "terms": {"field": "point_id"}},
                    "appliance_cnt": {
                        "terms": {"field": "appliance.keyword"}},
                }
            },
        }
    }
    log.info("alarm_content_info query_body={}".format(query_body))
    async with EsUtil() as es:
        es_result = await es.search_origin(body=query_body,
                                           index=constants.POINT_1MIN_EVENT)
    buckets = es_result["aggregations"]["alarm_cnt"]["buckets"]

    # 线路过载
    ele_overload = {"slots": [], "value": [0] * len(buckets)}
    # 违规电器接入
    illegal_ele_app = {"slots": [], "value": [0] * len(buckets)}
    # 电能品质
    power_quality = {"slots": [], "value": [0] * len(buckets)}
    # 电动车电池, 归为违规电器
    # ele_car_battery = {"slots": [], "value": [0] * len(buckets)}
    # 初始化,存储监测点,报警数量统计
    point_dic = {}
    for i in points:
        point_dic[i["name"]] = 0
    # 总报警数
    total_alarm_cnt = 0
    # 报警户数, 存在报警的point
    alarm_points = []
    # 违规电器统计
    illegal_app_dic = {}
    for index, bucket in enumerate(buckets):
        time_str = bucket["key_as_string"][5:10]
        ele_overload["slots"].append(time_str)
        illegal_ele_app["slots"].append(time_str)
        power_quality["slots"].append(time_str)
        # 1. 报警类型统计
        if bucket["type_cnt"]["buckets"]:
            for item in bucket["type_cnt"]["buckets"]:
                total_alarm_cnt += item["doc_count"]
                if item["key"] == "illegal_ele_app":
                    illegal_ele_app["value"][index] += item["doc_count"]
                if item["key"] == "ele_overload":
                    ele_overload["value"][index] += item["doc_count"]
                if item["key"] == "power_quality_low":
                    power_quality["value"][index] += item["doc_count"]
                # 电动车电池, 归为违规电器, 业务后台已处理
                # if item["key"] == "ele_car_battery":
                #     illegal_ele_app["value"][index] += item["doc_count"]

        # 2. 监测点,报警数量统计
        # if bucket["name_cnt"]["buckets"]:
        #     for item in bucket["name_cnt"]["buckets"]:
        #         point_dic[item["key"]] += item["doc_count"]

        # 3. 计算报警户数point
        if bucket["point_cnt"]["buckets"]:
            for item in bucket["point_cnt"]["buckets"]:
                point_t = item["key"]
                if point_t not in alarm_points:
                    alarm_points.append(point_t)

        # 4.小程序,违规电器统计
        if bucket["appliance_cnt"]["buckets"]:
            for item in bucket["appliance_cnt"]["buckets"]:
                illegal = item["key"]
                if illegal not in illegal_app_dic:
                    illegal_app_dic[illegal] = item["doc_count"]
                else:
                    illegal_app_dic[illegal] += item["doc_count"]
    log.info(f"ele_overload={ele_overload}")
    log.info(f"illegal_ele_app={illegal_ele_app}")
    log.info(f"power_quality={power_quality}")
    log.info(f"illegal_app_dic={illegal_app_dic}")
    log.info(f"total_alarm_cnt={total_alarm_cnt}")
    return {
        "ele_overload": ele_overload,
        "illegal_ele_app": illegal_ele_app,
        "power_quality": power_quality,
        "illegal_app_dic": illegal_app_dic,
        "total_alarm_cnt": total_alarm_cnt,
        "alarm_points_cnt": len(alarm_points)
    }


async def new_alarm_content_info_new15(cids, start, end, points):
    # 总报警数
    total_alarm_cnt = 0
    # 报警户数, 存在报警的point
    alarm_points = []
    # 违规电器统计
    illegal_app_dic = {}
    if len(cids) == 1:
        mid_sql = f" and cid = {cids[0]}"
    else:
        mid_sql = f" and cid in {tuple(cids)}"
    sql = f"SELECT DATE_FORMAT(event_datetime,'%m-%d') dt,event_type," \
          f"count(1) doc_count FROM `point_1min_event` where event_type in " \
          f"{tuple(SDU_ALARM_LIST)} and event_datetime BETWEEN '{start}' " \
          f"and '{end}' {mid_sql} GROUP BY dt,event_type ORDER BY dt"
    point_sql = f"SELECT DISTINCT pid FROM `point_1min_event` " \
                f"where event_type in {tuple(SDU_ALARM_LIST)} and " \
                f"event_datetime BETWEEN '{start}' and '{end}' {mid_sql} "
    appliance_sql = f"SELECT appliance,count(1) doc_count FROM " \
                    f"`point_1min_event` where event_type in " \
                    f"{tuple(SDU_ALARM_LIST)} and event_datetime " \
                    f"BETWEEN '{start}' and '{end}' {mid_sql} " \
                    f"GROUP BY appliance"
    log.info(f"sql:{sql}")
    log.info(f"point_sql:{point_sql}")
    log.info(f"appliance_sql:{appliance_sql}")
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
        point_datas = await conn.fetchall(point_sql)
        appliance_datas = await conn.fetchall(appliance_sql)
    if not datas:
        return {
            "ele_overload": {"slots": [], "value": []},
            "illegal_ele_app": {"slots": [], "value": []},
            "power_quality": {"slots": [], "value": []},
            "illegal_app_dic": illegal_app_dic,
            "total_alarm_cnt": total_alarm_cnt,
            "alarm_points_cnt": len(alarm_points)
        }
    # time_slots = list(set(str(data["dt"]) for data in datas))
    time_slots = proxy_power_slots(start, end, "MM-DD", True)
    log.info(f"time_slots:{time_slots}")
    # 线路过载
    ele_overload = {"slots": time_slots, "value": [0] * len(time_slots)}
    # 违规电器接入
    illegal_ele_app = {"slots": time_slots, "value": [0] * len(time_slots)}
    # 电能品质
    power_quality = {"slots": time_slots, "value": [0] * len(time_slots)}
    # 初始化,存储监测点,报警数量统计
    point_dic = {}
    for i in points:
        point_dic[i["name"]] = 0
    for data in datas:
        total_alarm_cnt += data["doc_count"]
        try:
            index = time_slots.index(str(data["dt"]))
        except Exception as e:
            log.error(f"new_alarm_content_info_new15 data {str(e)}")
            continue
        if data["event_type"] == "illegal_ele_app":
            illegal_ele_app["value"][index] += data["doc_count"]
        if data["event_type"] == "ele_overload":
            ele_overload["value"][index] += data["doc_count"]
        if data["event_type"] == "power_quality_low":
            power_quality["value"][index] += data["doc_count"]
    # 3. 计算报警户数point
    alarm_points = [point["pid"] for point in point_datas]
    # 4.小程序,违规电器统计
    for item in appliance_datas:
        illegal = item["appliance"]
        if illegal:
            if illegal not in illegal_app_dic:
                illegal_app_dic[illegal] = item["doc_count"]
            else:
                illegal_app_dic[illegal] += item["doc_count"]
    return {
        "ele_overload": ele_overload,
        "illegal_ele_app": illegal_ele_app,
        "power_quality": power_quality,
        "illegal_app_dic": illegal_app_dic,
        "total_alarm_cnt": total_alarm_cnt,
        "alarm_points_cnt": len(alarm_points)
    }


async def risk_distribution(start, end, point_id_list, is_new=False):
    """风险分布, 暂时识电u使用"""
    start_dt = datetime.strptime(start, "%Y-%m-%d %H:%M:%S")
    end_dt = datetime.strptime(end, "%Y-%m-%d %H:%M:%S")
    es_start_str = datetime(year=start_dt.year, month=start_dt.month,
                            day=start_dt.day).strftime(
        "%Y-%m-%dT%H:%M:%S+08:00")
    es_end_str = end_dt.strftime("%Y-%m-%dT%H:%M:%S+08:00")
    importance_list = [1]
    # 新版sdu, 其中当前时段发生过I或II级报警为风险用户，其余为安全用户
    if is_new:
        importance_list = [1, 2]
    query_body = {
        "size": 0,
        "query": {
            "bool": {
                "filter": [
                    {
                        "range": {
                            "datetime": {
                                "gte": es_start_str,
                                "lte": es_end_str
                            }
                        }
                    },
                    {
                        "terms": {
                            "point_id": point_id_list
                        }
                    },
                    {
                        "terms": {
                            "importance": importance_list
                        }
                    },
                    # 新版sdu, 限制报警类型
                    {"terms": {"type.keyword": SDU_ALARM_LIST}}
                ]
            }
        },
        "aggs": {
            "alarm_cnt": {
                "terms": {
                    "field": "point_id"
                }
            }
        }
    }
    log.info("risk_distribution query_body={}".format(query_body))
    async with EsUtil() as es:
        es_result = await es.search_origin(body=query_body,
                                           index=constants.POINT_1MIN_EVENT)
    buckets = es_result["aggregations"]["alarm_cnt"]["buckets"]
    total_user = len(point_id_list)
    risk_user = len(buckets)
    security_user = total_user - risk_user
    return security_user, risk_user


async def risk_distribution_new15(start, end, point_id_list, is_new=False):
    li = [f"event_datetime BETWEEN '{start}' and '{end}'",
          f"event_type in {tuple(SDU_ALARM_LIST)}"]
    if is_new:
        li.append("importance in (1, 2)")
    else:
        li.append("importance=1")
    if len(point_id_list) == 1:
        li.append(f"pid={point_id_list[0]}")
    else:
        li.append(f"pid in {tuple(point_id_list)}")
    mid_sql = " and ".join(li)
    sql = f"SELECT DISTINCT pid FROM `point_1min_event` where {mid_sql}"
    log.info(f"risk_distribution_new15 sql:{sql}")
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
    total_user = len(point_id_list)
    risk_user = len(datas)
    security_user = total_user - risk_user
    return security_user, risk_user


async def zdu_summary_info(cid, start, end):
    """
    智电u, 报警总数/报警监测点数
    """
    start_dt = datetime.strptime(start, "%Y-%m-%d %H:%M:%S")
    end_dt = datetime.strptime(end, "%Y-%m-%d %H:%M:%S")
    es_start_str = datetime(year=start_dt.year, month=start_dt.month,
                            day=start_dt.day).strftime(
        "%Y-%m-%dT%H:%M:%S+08:00")
    es_end_str = end_dt.strftime("%Y-%m-%dT%H:%M:%S+08:00")
    filter_list = [
        {"range": {"datetime": {"gte": es_start_str, "lte": es_end_str}}},
        {"term": {"cid": cid}},
    ]
    query_body = {
        "size": 0,
        "query": {"bool": {"filter": filter_list}},
        "aggs": {
            "alarm_cnt": {
                "terms": {
                    "field": "point_id"
                }
            }
        }
    }
    log.info("query_body query_body={}".format(query_body))
    async with EsUtil() as es:
        es_result = await es.search_origin(body=query_body,
                                           index=constants.POINT_1MIN_EVENT)
    buckets = es_result["aggregations"]["alarm_cnt"]["buckets"]

    # 总报警数
    total_alarm_cnt = 0
    for info in buckets:
        total_alarm_cnt += info["doc_count"]

    return total_alarm_cnt, len(buckets)  # 总监测点数
