from pot_libs.es_util.es_utils import EsUtil
from pot_libs.mysql_util.mysql_util import MysqlUtil
from pot_libs.utils.pendulum_wrapper import my_pendulum
from unify_api.utils.time_format import get_start_end_by_tz_time_new


def convert_es_str(str1: object) -> object:
    """str date转换为str es日期格式"""
    es_date = my_pendulum.from_format(str1, 'YYYY-MM-DD')
    return str(es_date)


async def query_search_kwh_p(cid, start, end, interval):
    query_body = {
        "query": {
            "bool": {
                "filter": [
                    {
                        "term": {
                            "cid": cid
                        }
                    },
                    {
                        "range": {
                            "quarter_time": {
                              "gte": convert_es_str(start),
                              "lte": convert_es_str(end)
                            }
                          }
                        }
                ]
            }
        },
        "aggs": {"quarter_time": {
            "date_histogram": {
                "field": "quarter_time",
                "interval": interval,
                "time_zone": "+08:00",
                "format": "yyyy-MM-dd HH:mm:ss"
            },
            "aggs": {
                "p": {
                    "stats": {
                        "field": "p"
                    }
                },
                "kwh": {
                    "stats": {
                        "field": "kwh"
                    }
                }
            }
        }
        },
        "sort": [
            {
                "quarter_time": {
                    "order": "asc"
                }
            }
        ]
    }
    async with EsUtil() as es:
        es_re = await es.search_origin(body=query_body,
                                       index="poweriot_company_15min_power")
    return es_re["aggregations"]["quarter_time"]["buckets"]


async def query_search_kwh_p_new15(cid, start, end):
    start, _ = get_start_end_by_tz_time_new(start)
    _, end = get_start_end_by_tz_time_new(end)
    sql = f"""
        select p,kwh,create_time from company_15min_power
        where cid = %s and create_time BETWEEN %s and %s
        order by create_time asc
    """
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql=sql,
                                    args=(cid, start, end))
    return datas


async def query_spfv_price(cid, start, end):
    query_body = {
        "query": {
            "bool": {
                "filter": [
                    {
                        "term": {
                            "cid": cid
                        }
                    },
                    {
                        "range": {
                            "quarter_time": {
                              "gte": convert_es_str(start),
                              "lte": convert_es_str(end)
                            }
                          }
                        }
                ]
            }
        },
        "aggs": {
                "charge": {
                    "stats": {
                        "field": "charge"
                    }
                },
                "kwh": {
                    "stats": {
                        "field": "kwh"
                    }
                }

            }
    }
    async with EsUtil() as es:
        es_re = await es.search_origin(body=query_body,
                                       index="poweriot_company_15min_power")
    return es_re["aggregations"]["charge"]["avg"], es_re["aggregations"]["kwh"]["avg"]


async def query_spfv_price_new15(cid, start, end):
    start, _ = get_start_end_by_tz_time_new(start)
    _, end = get_start_end_by_tz_time_new(end)
    sql = f"""
            select avg(charge) charge,avg(kwh) kwh from company_15min_power
            where cid = %s and create_time BETWEEN %s and %s
        """
    async with MysqlUtil() as conn:
        data = await conn.fetchone(sql=sql, args=(cid, start, end))
    return data.get('charge', 0), data.get('kwh', 0)
