import json
from pot_libs.aredis_util.aredis_utils import RedisUtils
from pot_libs.mysql_util.mysql_util import MysqlUtil
from pot_libs.es_util.es_utils import EsUtil
from pot_libs.logger import log
from unify_api.utils.time_format import convert_es_str

TSP_CURRENT = "meterdata_tsp_current"
TSP_15MIN = "poweriot_tsp_15min"


async def meterdata_tsp_current(tsp_id):
    """根据tsp_id获取redis实时数据"""
    res = await RedisUtils().hget(TSP_CURRENT, tsp_id)
    return json.loads(res)


async def tsp_histogram_tsp_id(date_start, date_end, tsp_id, interval):
    """TSP信息-历史曲线"""
    start_es = convert_es_str(date_start)
    end_es = convert_es_str(date_end)

    query_body = {
        "size": 0,
        "query": {
            "bool": {
                "must": [
                    {
                        "term": {
                            "tsp_id": tsp_id
                        }
                    },
                    {
                        "range": {
                            "quarter_time": {
                                "gte": start_es,
                                "lte": end_es
                            }
                        }
                    }
                ]
            }
        },
        "aggs": {
            "quarter_time": {
                "date_histogram": {
                    "field": "quarter_time",
                    "interval": interval,
                    "time_zone": "+08:00",
                    "format": "yyyy-MM-dd HH:mm:ss"
                },
                "aggs": {
                    "pm25": {
                        "stats": {
                            "field": "pm25_max"
                        }
                    },
                    "pm10": {
                        "stats": {
                            "field": "pm10_max"
                        }
                    },
                    "tsp": {
                        "stats": {
                            "field": "tsp_max"
                        }
                    }
                }
            }
        }
    }
    log.info(query_body)
    async with EsUtil() as es:
        es_re = await es.search_origin(body=query_body, index=TSP_15MIN)
    return es_re["aggregations"]["quarter_time"]["buckets"]


async def tsp_index_statistics(date_start, date_end, tsp_id):
    """TSP信息-指标统计"""
    start_es = convert_es_str(date_start)
    end_es = convert_es_str(date_end)

    query_body = {
        "query": {
            "bool": {
                "must": [
                    {
                        "term": {
                            "tsp_id": tsp_id
                        }
                    },
                    {
                        "range": {
                            "quarter_time": {
                                "gte": start_es,
                                "lte": end_es
                            }
                        }
                    }
                ]
            }
        },
        "size": 0,
        "aggs": {
            "pm25_max": {
                "top_hits": {
                    "sort": [
                        {
                            "pm25_max": {
                                "order": "desc"
                            }
                        }
                    ],
                    "size": 1,
                    "_source": ["pm25_max", "pm25_max_time"]
                }
            },
            "pm25_min": {
                "top_hits": {
                    "sort": [
                        {
                            "pm25_min": {
                                "order": "asc"
                            }
                        }
                    ],
                    "size": 1,
                    "_source": ["pm25_min", "pm25_min_time"]
                }
            },
            "pm10_max": {
                "top_hits": {
                    "sort": [
                        {
                            "pm10_max": {
                                "order": "desc"
                            }
                        }
                    ],
                    "size": 1,
                    "_source": ["pm10_max", "pm10_max_time"]
                }
            },
            "pm10_min": {
                "top_hits": {
                    "sort": [
                        {
                            "pm10_min": {
                                "order": "asc"
                            }
                        }
                    ],
                    "size": 1,
                    "_source": ["pm10_min", "pm10_min_time"]
                }
            },
            "tsp_max": {
                "top_hits": {
                    "sort": [
                        {
                            "tsp_max": {
                                "order": "desc"
                            }
                        }
                    ],
                    "size": 1,
                    "_source": ["tsp_max", "tsp_max_time"]
                }
            },
            "tsp_min": {
                "top_hits": {
                    "sort": [
                        {
                            "tsp_min": {
                                "order": "asc"
                            }
                        }
                    ],
                    "size": 1,
                    "_source": ["tsp_min", "tsp_min_time"]
                }
            },
            "pm25_avg": {
                "avg": {
                    "field": "pm25_max"
                }
            },
            "pm10_avg": {
                "avg": {
                    "field": "pm10_max"
                }
            },
            "tsp_avg": {
                "avg": {
                    "field": "tsp_max"
                }
            }
        }
    }
    log.info(query_body)
    async with EsUtil() as es:
        es_re = await es.search_origin(body=query_body, index=TSP_15MIN)
    return es_re["aggregations"]


async def tsp_aggs_tsp_id(date_start, date_end, tsp_list):
    """1. 按tsp_id聚合
       2. 分别求平均值
    """
    start_es = convert_es_str(date_start)
    end_es = convert_es_str(date_end)

    query_body = {
        "size": 0,
        "query": {
            "bool": {
                "must": [
                    {
                        "terms": {
                            "tsp_id": tsp_list
                        }
                    },
                    {
                        "range": {
                            "quarter_time": {
                                "gte": start_es,
                                "lte": end_es
                            }
                        }
                    }
                ]
            }
        },
        "aggs": {
            "tsps": {
                "terms": {
                    "field": "tsp_id",
                    "size": 1000
                },
                "aggs": {
                    "pm25": {
                        "avg": {
                            "field": "pm25_mean"
                        }
                    },
                    "pm10": {
                        "avg": {
                            "field": "pm10_mean"
                        }
                    },
                    "tsp": {
                        "avg": {
                            "field": "tsp_mean"
                        }
                    }
                }
            }
        }
    }
    log.info(query_body)
    async with EsUtil() as es:
        es_re = await es.search_origin(body=query_body, index=TSP_15MIN)
    return es_re["aggregations"]["tsps"]["buckets"]


async def tsp_by_tsp_id_dao(start, end, tsp_list):
    sql = f'SELECT tsp_id, ' \
          f'AVG(pm25_mean) pm25,AVG(pm10_mean) pm10,AVG(tsp_mean) tsp ' \
          f'FROM `tsp_day_record` where tsp_id in %s and ' \
          f'create_time BETWEEN "{start}" and "{end}" GROUP BY tsp_id '
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(tsp_list, ))
    return datas


async def tsp_histogram_day_tsp_id(interval):
    """空气优-按天聚合, 再按tsp_id聚合"""
    query_body = {
        "size": 0,
        "aggs": {
            "quarter_time": {
                "date_histogram": {
                    "field": "quarter_time",
                    "interval": interval,
                    "time_zone": "+08:00",
                    "format": "yyyy-MM-dd HH:mm:ss"
                },
                "aggs": {
                    "tsps": {
                        "terms": {
                            "field": "tsp_id",
                            "size": 1000
                        },
                        "aggs": {
                            "pm25": {
                                "avg": {
                                    "field": "pm25_mean"
                                }
                            },
                            "pm10": {
                                "avg": {
                                    "field": "pm10_mean"
                                }
                            },
                            "tsp": {
                                "avg": {
                                    "field": "tsp_mean"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    log.info(query_body)
    async with EsUtil() as es:
        es_re = await es.search_origin(body=query_body, index=TSP_15MIN)
    return es_re["aggregations"]["quarter_time"]["buckets"]


async def tsp_histogram_avg(date_start, date_end, interval, tsp_id=None):
    """TSP信息-历史曲线-莫天是1h一个点的平均值"""
    start_es = convert_es_str(date_start)
    end_es = convert_es_str(date_end)

    query_body = {
        "size": 0,
        "query": {
            "bool": {
                "must": [
                    {
                        "range": {
                            "quarter_time": {
                                "gte": start_es,
                                "lte": end_es
                            }
                        }
                    }
                ]
            }
        },
        "aggs": {
            "quarter_time": {
                "date_histogram": {
                    "field": "quarter_time",
                    "interval": interval,
                    "time_zone": "+08:00",
                    "format": "yyyy-MM-dd HH:mm:ss"
                },
                "aggs": {
                    "pm25": {
                        "avg": {
                            "field": "pm25_mean"
                        }
                    },
                    "pm10": {
                        "avg": {
                            "field": "pm10_mean"
                        }
                    },
                    "tsp": {
                        "avg": {
                            "field": "tsp_mean"
                        }
                    }
                }
            }
        }
    }

    if tsp_id:
        query_body["query"]["bool"]["must"].append(
            {"term": {"tsp_id": tsp_id}}
        )

    log.info(query_body)
    async with EsUtil() as es:
        es_re = await es.search_origin(body=query_body, index=TSP_15MIN)
    return es_re["aggregations"]["quarter_time"]["buckets"]


async def range_max_value(date_start, date_end):
    """今日最高PM2.5"""
    start_es = convert_es_str(date_start)
    end_es = convert_es_str(date_end)

    query_body = {
        "size": 0,
        "query": {
            "bool": {
                "must": [
                    {
                        "range": {
                            "quarter_time": {
                                "gte": start_es,
                                "lte": end_es
                            }
                        }
                    }
                ]
            }
        },
        "aggs": {
            "pm25": {
                "max": {
                    "field": "pm25_max"
                }
            },
            "pm10": {
                "avg": {
                    "field": "pm10_max"
                }
            },
            "tsp": {
                "avg": {
                    "field": "tsp_max"
                }
            }
        }
    }

    log.info(query_body)
    async with EsUtil() as es:
        es_re = await es.search_origin(body=query_body, index=TSP_15MIN)
    return es_re
