from pot_libs.logger import log
from unify_api.constants import SDU_ALARM_LIST
from pot_libs.mysql_util.mysql_util import MysqlUtil


async def sdu_alarm_statistics_dao(cid, start, end, is_sdu=True):
    mid_sql = ""
    if is_sdu:
        # 新版sdu, 限制报警类型
        mid_sql = f" and event_type in {tuple(SDU_ALARM_LIST)}"
    sql = f"SELECT pid,appliance,count(1) doc_count FROM `point_1min_event` " \
          f"where cid= {cid} and event_datetime BETWEEN '{start}' and '{end}' " \
          f"{mid_sql} GROUP BY pid, appliance ORDER BY pid"
    log.info(f"sql:{sql}")
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
    return datas


async def sdu_alarm_type_dao(start, end, points):
    if points and len(points) == 1:
        mid_str = f"pid={points[0]}"
    else:
        mid_str = f"pid in {tuple(points)}"
    sql = f"SELECT pid,event_type,count(1) doc_count FROM point_1min_event " \
          f"where {mid_str} " \
          f"and event_datetime BETWEEN '{start}' and '{end}' " \
          f"GROUP BY pid, event_type ORDER BY pid"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
    return datas


async def sdu_alarm_limit_type_dao(start, end, points):
    sql = f"SELECT pid, event_type, count(1) doc_count " \
          f"FROM `point_1min_event` WHERE pid in %s and " \
          f"event_datetime BETWEEN '{start}' and '{end}' " \
          f"and event_type in {tuple(SDU_ALARM_LIST)} GROUP BY pid, event_type"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(points,))
    return datas


async def sdu_alarm_importance_dao(start, end, points, is_sdu=None):
    if points and len(points) == 1:
        mid_str = f"pid={points[0]}"
    else:
        mid_str = f"pid in {tuple(points)}"
    if is_sdu:
        mid_str += f" and event_type in {tuple(SDU_ALARM_LIST)}"
    sql = f"SELECT pid,importance,count(1) doc_count FROM point_1min_event " \
          f"where {mid_str} " \
          f"and event_datetime BETWEEN '{start}' and '{end}' " \
          f"GROUP BY pid, importance ORDER BY pid"
    log.info(f"sql:{sql}")
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
    return datas


async def sdu_alarm_behavior_dao(start, end, points):
    sql = f"SELECT appliance, count(1) doc_count " \
          f"FROM `point_1min_event` WHERE pid in %s " \
          f"and event_type = 'illegal_ele_app' and " \
          f"event_datetime BETWEEN '{start}' and '{end}'GROUP BY appliance"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(points,))
    return datas


async def zdu_alarm_aggs_date_impotent(cid, start, end):
    """zdu求安全运行
       根据每日聚合,再根据等级聚合
    """
    sql = f"""
        SELECT DISTINCT
            DATE_FORMAT( event_datetime, '%m-%d' ) dat
        FROM
            point_1min_event 
        WHERE
            cid = {cid} 
            AND event_datetime BETWEEN '{start}' 
            AND '{end}' 
            AND importance !=1
         
    """
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
    return len(datas) if datas else 0


async def sdu_alarm_aggs_type(cid, start, end):
    sql = f"""
        SELECT 
            COUNT(*) doc_count, 
            event_type
        FROM
            point_1min_event pevent
        WHERE
            cid = {cid}
        AND pevent.event_datetime >= '{start}'
        AND pevent.event_datetime <= '{end}'
        GROUP BY
            pevent.event_type;
    """
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
    return datas if datas else []


async def alarm_aggs_importance(cid, start, end):
    """按报警等级聚合"""
    sql = f"""
        SELECT
            COUNT(*) alarm_count,
            importance
        FROM
            point_1min_event pevent
        WHERE
            cid = {54}
        AND pevent.event_datetime >= '{start}'
        AND pevent.event_datetime <= '{end}'
        GROUP BY
            pevent.importance
    """
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
    return datas if datas else []
