from pot_libs.mysql_util.mysql_util import MysqlUtil
from pot_libs.logger import log


async def get_predict_data_day_dao(tsp_id, start, end):
    sql = "SELECT tsp_id,quarter_time,pm10,pm25,tsp " \
          "FROM `tsp_15min_forecast` where tsp_id=%s and " \
          "quarter_time >= %s and quarter_time < %s"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(tsp_id, start, end))
    return datas


async def get_predict_data_month_dao(tsp_id, start, end):
    sql = f"SELECT  DATE_FORMAT(quarter_time, '%Y-%m-%d') quarter_time, " \
          f"AVG(pm10) pm10,AVG(pm25) pm25,AVG(tsp) tsp " \
          f"FROM `tsp_15min_forecast` where tsp_id={tsp_id} and " \
          f"quarter_time >= '{start}' and quarter_time < '{end}'  " \
          "GROUP BY DATE_FORMAT(quarter_time, '%Y-%m-%d')"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
    return datas


async def get_contrast_data_day_dao(tsp_id, start, end):
    sql = "SELECT tsp_id,quarter_time,pm10,pm25 " \
          "FROM `tsp_15min_contrast` where tsp_id=%s and " \
          "quarter_time >= %s and quarter_time < %s"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(tsp_id, start, end))
    return datas


async def get_contrast_data_month_dao(tsp_id, start, end):
    sql = f"SELECT  DATE_FORMAT(quarter_time, '%Y-%m-%d') quarter_time, " \
          f"AVG(pm10) pm10,AVG(pm25) pm25 " \
          f"FROM `tsp_15min_forecast` where tsp_id={tsp_id} and " \
          f"quarter_time >= '{start}' and quarter_time < '{end}'  " \
          "GROUP BY DATE_FORMAT(quarter_time, '%Y-%m-%d')"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
    return datas


async def get_index_data_dao():
    sql = "SELECT DISTINCT c.cid, c.shortname, c.fullname, c.industry, " \
          "c.province FROM tsp t LEFT JOIN company c on t.cid=c.cid"
    async with MysqlUtil() as conn:
        companys = await conn.fetchall(sql)
    return companys


async def get_position_dao(company_list):
    sql = "SELECT cid, value FROM `company_extend` where " \
          "`key`= 'map_center_yangchen' and cid in %s;"
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql, args=(company_list,))
    return data


async def get_tsp_id_list_dao(company_list):
    sql = "select cid cid,group_concat(tsp_id) tsp_ids  from tsp " \
          "WHERE cid in %s GROUP BY cid"
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql, args=(company_list,))
    return data


async def get_month_rank_dao(cids, start, end):
    sql = f"SELECT cid, SUM(is_valid) effect, count(id) measures " \
          f"FROM `dust_1hour_measure` where cid in %s and " \
          f"start_datetime >= %s and start_datetime < %s GROUP BY cid"
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql, args=(cids, start, end))
    return data


async def get_cid_tsp_dao(cid, start, end, measure_type):
    sql = f"SELECT SUM(is_valid) effect, count(id) measures " \
          f"FROM `dust_1hour_measure` where cid = %s and " \
          f"start_datetime >= %s and start_datetime < %s and measure_type=%s"
    async with MysqlUtil() as conn:
        data = await conn.fetchone(sql, args=(cid, start, end, measure_type))
    return data


async def get_page_data(cid, start, end, page_num, page_size, measure_type):
    sql = "SELECT cid, start_datetime, end_datetime, measure_msg, is_valid, " \
          "effect FROM `dust_1hour_measure` where cid=%s and " \
          "measure_type=%s and start_datetime>=%s and start_datetime<=%s " \
          "ORDER BY start_datetime desc limit %s, %s"
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql,
                                   args=(cid, measure_type, start, end,
                                         (page_num-1)*page_size, page_size))
    return data