import pendulum
from pot_libs.es_util.es_utils import EsUtil
from pot_libs.mysql_util.mysql_util import MysqlUtil
from pot_libs.logger import log
from unify_api.constants import POINT_1MIN_EVENT, SDU_ALARM_LIST
from unify_api.utils.time_format import convert_es_str

index = POINT_1MIN_EVENT


async def new_list_alarm_dao(cid, points, start, end, importance, offset,
                             page_size):
    mid_li = [f"cid={cid}", f"event_type in {tuple(SDU_ALARM_LIST)}"]
    if len(points) == 1:
        mid_li.append(f"pid={points[0]}")
    else:
        mid_li.append(f"pid in {tuple(points)}")
    if len(importance) > 0:
        importance = str(tuple(importance)).replace(',)', ')')
        mid_li.append(f"importance in {importance}")
    if start and end:
        mid_li.append(f"event_datetime BETWEEN '{start}' and '{end}'")
    else:
        now_date = pendulum.now()
        time_format = "%Y-%m-%d %H:%M:%S"
        end_date = str(now_date.strftime(time_format))
        mid_li.append(f"event_datetime < '{end_date}'")
    # 查询总数
    total_sql = f"""
        select count(*) total_count from point_1min_event where {' and '.join(mid_li)}
    """
    async with MysqlUtil() as conn:
        total = await conn.fetchone(total_sql)
    if total.get('total_count', 0) <= 0:
        return 0, []
    # 查询详情
    sql = f"""
        select * from point_1min_event where {' and '.join(mid_li)}
        order by event_datetime desc limit %s,%s
    """
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql, args=(offset, page_size))
    return total.get('total_count', 0), data


async def wx_list_alarm_dao(cids, product, start, end):
    """小程序消息列表, 取当前范围最新40条"""

    # alarm_list = list(EVENT_TYPE_MAP.keys())  # 安电u
    #
    # if product == Product.RecognitionElectric.value:  # 识电u
    alarm_list = SDU_ALARM_LIST

    query_body = {
        "size": 40,
        "query": {
            "bool": {
                "must": [
                    {
                        "terms": {
                            "cid": cids
                        }
                    },
                    {
                        "terms": {
                            "type.keyword": alarm_list
                        }
                    }
                ]
            }
        },
        "sort": [
            {
                "datetime": {
                    "order": "desc"
                }
            }
        ]
    }
    if start and end:
        start_es = convert_es_str(start)
        end_es = convert_es_str(end)
        query_body["query"]["bool"]["must"].append(
            {
                "range": {
                    "datetime": {
                        "gte": start_es,
                        "lte": end_es
                    }
                }
            }
        )

    log.info(f"index:{index}--query_body:{query_body}")
    async with EsUtil() as es:
        es_re = await es.search_origin(body=query_body, index=index)
    return es_re


async def list_alarm_zdu_dao(cid, points, start, end, importance, event_type):
    mid_li = [f"cid={cid}"]
    if len(points) == 1:
        mid_li.append(f"pid={points[0]}")
    else:
        mid_li.append(f"pid in {tuple(points)}")
    if len(importance) == 1:
        mid_li.append(f"importance={importance[0]}")
    else:
        mid_li.append(f"importance in {tuple(importance)}")
    if event_type:
        if len(event_type) == 1:
            mid_li.append(f"event_type={event_type[0]}")
        else:
            mid_li.append(f"event_type in {tuple(event_type)}")
    if start and end:
        mid_li.append(f"event_datetime BETWEEN '{start}' and '{end}'")
    sql = f"select * from point_1min_event where {' and '.join(mid_li)} " \
          f"order by event_datetime desc "
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql)
    return data


async def wx_list_alarm_zdu_dao(cid, points, start, end):
    """智电u, wx小程序, 取当前范围最新100条"""

    query_body = {
        "size": 100,
        "query": {
            "bool": {
                "must": [
                    {
                        "term": {
                            "cid": cid
                        }
                    },
                    {
                        "terms": {
                            "point_id": points
                        }
                    }
                ]
            }
        },
        "sort": [
            {
                "datetime": {
                    "order": "desc"
                }
            }
        ]
    }
    if start and end:
        start_es = convert_es_str(start)
        end_es = convert_es_str(end)
        query_body["query"]["bool"]["must"].append(
            {
                "range": {
                    "datetime": {
                        "gte": start_es,
                        "lte": end_es
                    }
                }
            }
        )
    log.info(f"index:{index}--query_body:{query_body}")
    async with EsUtil() as es:
        es_re = await es.search_origin(body=query_body, index=index)
    return es_re


async def zdu_alarm_sort_dao(cid, start, end, page_size, page_num):
    """
        智电告警排名
    """
    sql = f"""
        SELECT
            pt.`name`,
            a.event_count 
        FROM
            point pt
            RIGHT JOIN (
            SELECT
                pid,
                COUNT(*) event_count
            FROM
                point_1min_event 
            WHERE
                cid = {cid} 
                AND event_datetime > "{start}" 
                AND event_datetime < "{end}" 
                AND event_type in {tuple(SDU_ALARM_LIST)}
            GROUP BY
                pid 
            ORDER BY
                event_count DESC 
            ) a ON pt.pid = a.pid limit {(page_num - 1) * page_size}, {page_size}
    """
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql, args=())
    return data


async def alarm_content_time_distribution_dao(cid, start, end, ):
    """
    智电温度漏电流电参数分布
    """
    if isinstance(cid, list):
        mid_li = [f"cid in {str(tuple(cid)).replace(',)', ')')}"]
    else:
        mid_li = [f"cid={cid}"]
    if start and end:
        mid_li.append(f"event_datetime BETWEEN '{start}' and '{end}'")

    sql = f"""
            SELECT
                event_type,
                DATE_FORMAT( event_datetime, '%m-%d' ) dat,
                count(*) coun 
            FROM
                power_iot.point_1min_event 
            WHERE
                {' and '.join(mid_li)}
            GROUP BY
                DATE_FORMAT( event_datetime, '%m-%d' ),
                event_type 
            ORDER BY
                dat ASC
        """
    async with MysqlUtil() as conn:
        elec_data = await conn.fetchall(sql, )
    return elec_data if elec_data else []


async def zdu_summary_dao(cid, start, end):
    sql = f"""
        SELECT
            pid,
            count(*) coun
            
        FROM
            point_1min_event 
        WHERE
            cid = {cid} 
            AND event_datetime BETWEEN '{start}' 
            AND '{end}' AND pid is not null
        GROUP BY
            pid
    """
    async with MysqlUtil() as conn:
        summary_data = await conn.fetchall(sql, )
        total_alarm_cnt = sum([i.get('coun') for i in summary_data])
        alarm_points = len(summary_data)
    return total_alarm_cnt, alarm_points
