import pendulum
from pot_libs.mysql_util.mysql_util import MysqlUtil
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, start, end):
    """小程序消息列表, 取当前范围最新40条"""
    cid = cids[0] if isinstance(cids, (list, tuple)) else cids
    cond_lst = [f"cid={cid}", f"event_type in {tuple(SDU_ALARM_LIST)}"]
    if start and end:
        s_dts = convert_es_str(start)
        e_dts = convert_es_str(end)
        cond_lst.append(f"event_datetime BETWEEN '{s_dts}' and '{e_dts}'")
    else:
        now_date = pendulum.now()
        time_format = "%Y-%m-%d %H:%M:%S"
        end_date = str(now_date.strftime(time_format))
        cond_lst.append(f"event_datetime < '{end_date}'")

    cond_str = " AND ".join(cond_lst)
    async with MysqlUtil() as conn:
        sql = f"select count(*) cnt from point_1min_event WHERE {cond_str};"
        total = await conn.fetchone(sql)
        total_count = total.get("cnt", 0)
        if total_count <= 0:
            return 0, []

        sql = f"select * from point_1min_event WHERE {cond_str} " \
              f"order by event_datetime desc limit 40;"
        data = await conn.fetchall(sql)
        return total_count, data


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条"""
    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 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 limit 100;"
    async with MysqlUtil() as conn:
        return await conn.fetchall(sql)


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
