import pendulum

from pot_libs.mysql_util.mysql_util import MysqlUtil


async def proxy_health_sumarry(cids, month_str):
    health_stats_list, point_ids = [], []
    if cids:
        async with MysqlUtil() as conn:
            sql = "select * from safe_health_stats_point " \
                  "where cid in %s and cal_month=%s"
            health_stats_list = await conn.fetchall(sql, args=(cids, month_str,))

            month_start_time = pendulum.from_format(month_str, "YYYY-MM",
                                                    tz="Asia/Shanghai")
            month_end_time = month_start_time.subtract(months=-1)
            mettimestamp = month_end_time.int_timestamp
            point_sql = "select p.pid, p.cid, m.demolished " \
                        "from point p LEFT JOIN monitor m on p.mtid=m.mtid " \
                        "where p.cid in %s " \
                        "and p.create_time < %s and m.demolished=0"
            points = await conn.fetchall(point_sql, args=(cids, mettimestamp))
            point_ids = [point["pid"] for point in points]

    unqualified_point_cnt = 0
    health_problem_map = {
        "volt_hr": 0,
        "curr_hr": 0,
        "ubl": 0,
        "ibl": 0,
        "volt_dev": 0,
        "freq_dev": 0,
    }
    health_level_map = {
        "excellent_cnt": 0,
        "good_cnt": 0,
        "fair_cnt": 0,
        "bad_cnt": 0,
    }

    for i in health_stats_list:
        stand_volt_hr = 5
        stand_curr_hr = 15
        stand_ubl = 2
        stand_ibl = 5

        unqualified_point = False
        if abs(i["volt_hr"] * 100) > stand_volt_hr:
            health_problem_map["volt_hr"] += 1
            unqualified_point = True
        if abs(i["curr_hr"] * 100) > stand_curr_hr:
            health_problem_map["curr_hr"] += 1
            unqualified_point = True
        if abs(i["ubl"] * 100) > stand_ubl:
            health_problem_map["ubl"] += 1
            unqualified_point = True
        if abs(i["ibl"] * 100) > stand_ibl:
            health_problem_map["ibl"] += 1
            unqualified_point = True
        if abs(i["volt_dev"] * 100) > 5:
            health_problem_map["volt_dev"] += 1
            unqualified_point = True
        if abs(i["freq_dev"]) > 0.1:
            health_problem_map["freq_dev"] += 1
            unqualified_point = True
        if unqualified_point:
            unqualified_point_cnt += 1

    cid_healths = []
    if cids:
        async with MysqlUtil() as conn:
            sql = (
                "select cid, health_exp from safe_health_stats_cid "
                "where cid in %s and cal_month=%s"
            )
            cid_healths = await conn.fetchall(sql, args=(cids, month_str))
    for cid_health in cid_healths:
        health_index = round(cid_health["health_exp"])
        if 90 <= health_index <= 100:
            health_level_map["excellent_cnt"] += 1
        elif 75 <= health_index < 90:
            health_level_map["good_cnt"] += 1
        elif 60 <= health_index < 75:
            health_level_map["fair_cnt"] += 1
        else:
            health_level_map["bad_cnt"] += 1
    return {
        "total_point_cnt": len(point_ids),
        "unqualified_point_cnt": unqualified_point_cnt,
        "health_level": health_level_map,
        "health_problem": health_problem_map,
    }


async def proxy_page_point_healths(
    cids, month_str, page_size, page_num, sort_field="health_exp",
        sort_direction="asc"
):
    if not cids:
        return {"total": 0, "rows": []}
    async with MysqlUtil() as conn:
        sql = "select * from safe_health_stats_point " \
              "where cid in %s and cal_month=%s order by %s"
        health_stats_list = await conn.fetchall(
            sql, args=(cids, month_str, sort_field))
        if health_stats_list and sort_field in [
            "health_exp",
            "volt_hr",
            "curr_hr",
            "ubl",
            "ibl",
            "volt_dev",
            "freq_dev",
        ]:
            health_stats_list.sort(
                key=lambda i: i[sort_field], reverse=True
                if sort_direction == "desc" else False
            )
        point_ids = [i["pid"] for i in health_stats_list]
        unqualified_stats_list = []
        for i in health_stats_list:
            stand_volt_hr = 5
            stand_curr_hr = 15
            stand_ubl = 2
            stand_ibl = 5

            i["stand_volt_hr"] = stand_volt_hr
            i["stand_curr_hr"] = stand_curr_hr
            i["stand_ubl"] = stand_ubl
            i["stand_ibl"] = stand_ibl

            unqualified_point = False
            if abs(i["volt_hr"] * 100) > stand_volt_hr:
                unqualified_point = True
            if abs(i["curr_hr"] * 100) > stand_curr_hr:
                unqualified_point = True
            if abs(i["ubl"] * 100) > stand_ubl:
                unqualified_point = True
            if abs(i["ibl"] * 100) > stand_ibl:
                unqualified_point = True
            if abs(i["volt_dev"] * 100) > 5:
                unqualified_point = True
            if abs(i["freq_dev"]) > 0.1:
                unqualified_point = True
            if unqualified_point:
                unqualified_stats_list.append(i)

        total = len(unqualified_stats_list)
        points = []
        if point_ids:
            point_sql = "select pid, name from point where pid in %s"
            points = await conn.fetchall(point_sql, args=(point_ids,))
        point_map = {i["pid"]: i for i in points}

        company_sql = "select cid, shortname from company where cid in %s"
        companys = await conn.fetchall(company_sql, args=(cids,))
        company_map = {i["cid"]: i for i in companys}
    return {
        "total": total,
        "rows": [
            {
                "company_name": company_map[i["cid"]]["shortname"],
                "point_name": point_map.get(i["pid"], {}).get("name"),
                "health_index": i["health_exp"],
                "volt_hr": round(100 * i["volt_hr"], 1),
                "curr_hr": round(100 * i["curr_hr"], 1),
                "ubl": round(100 * i["ubl"], 1),
                "ibl": round(100 * i["ibl"], 1),
                "volt_dev": round(100 * i["volt_dev"], 1),
                "freq_dev": round(i["freq_dev"], 2),
                "stand_volt_hr": i["stand_volt_hr"],
                "stand_curr_hr": i["stand_curr_hr"],
                "stand_ubl": i["stand_ubl"],
                "stand_ibl": i["stand_ibl"],
                "stand_volt_dev": 5,
                "stand_freq_dev": 0.1,
            }
            for i in unqualified_stats_list[(page_num - 1) * page_size : page_num * page_size]
        ],
    }