from datetime import datetime

from pot_libs.es_util.es_utils import EsUtil
from pot_libs.mysql_util.mysql_util import MysqlUtil
from unify_api import constants


async def month_md_space(inline_id: int, month_list: list):
    sql = (
        "select a.month, a.inline_md_charge, a.inline_tc_charge, "
        "a.inline_md_predict from `algo_md_space_analysis_result` a "
        "inner join`algo_md_space_analysis_unit` b "
        " on a.space_analysis_id=b.id "
        "where b.inlid=%s and a.month in %s and b.valid=1"
    )
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(inline_id, month_list))
        md_space_map = {md["month"].strftime("%Y-%m-%d"): md for md in datas}
    return md_space_map


async def month_power_factors(inline_id: int, month_list: list):
    async with MysqlUtil() as conn:
        sql = "select `cos`, `month` res_time, save_charge pf_cost, " \
              "`save_charge` from algo_power_factor_result where `inlid`=%s" \
              " and `month` in %s"
        power_factors = await conn.fetchall(sql, args=(inline_id, month_list))
        power_factors_map = {pf["res_time"]: pf for pf in power_factors}
    return power_factors_map


async def month_power_pcvf(inline_id: int, month_list: list):
    sql = "select `month`, `score`, `cost_save` from " \
          "`algo_plsi_result` where `inlid`=%s and `month` in %s"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(inline_id, month_list))
    power_pcvf_map = {pf["month"].strftime("%Y-%m-%d"): pf for pf in datas}
    return power_pcvf_map


async def month_power_loadrate(inline_id: int, month_list: list):
    sql = "select `month`, `mean_load_factor` from " \
          "`algo_economic_operation_result` where `inlid`=%s and " \
          "`month` in %s"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(inline_id, month_list))
        power_loadrate = {pl["month"].strftime("%Y-%m-%d"): pl for pl in datas}
    return power_loadrate


async def inline_power_use_info(inline_id, es_start_time, es_end_time):
    """
    获取进线用电信息
    :param inline_id:
    :param es_start_time:
    :param es_end_time:
    :return:
    """
    async with EsUtil() as es:
        filters = [
            {"term": {"inlid": inline_id}},
            {"range": {"quarter_time": {"gte": es_start_time, "lt": es_end_time,}}},
        ]
        query_body = {
            "query": {"bool": {"filter": filters}},
            "size": 0,
            "aggs": {"kwh": {"sum": {"field": "kwh"}}, "charge": {"sum": {"field": "charge"}},},
        }
        es_result = await es.search_origin(body=query_body,
                                           index=constants.INLINE_15MIN_POWER)
        return {
            "charge": es_result["aggregations"]["charge"]["value"],
            "kwh": es_result["aggregations"]["kwh"]["value"],
        }


async def inline_power_use_info_new15(inline_id, start, end):
    """
    1.5版本获取进线用电信息
    :param inline_id:
    :param start:
    :param end:
    :return:
    """
    sql = f"SELECT sum(kwh) kwh, sum(charge) charge FROM inline_15min_power" \
          f" where inlid=%s and create_time BETWEEN '{start}' and '{end}'"
    async with MysqlUtil() as conn:
        datas = await conn.fetchone(sql, args=(inline_id, ))
    return datas
