from pot_libs.mysql_util.mysql_util import MysqlUtil
from unify_api.modules.elec_charge.components.elec_statistics_cps import SlotValue
from unify_api.constants import SLOTS
from unify_api.utils import time_format


async def query_dust_fogcan_run_state(pids, now_ymd):
    """根据pids查询最新雾炮结果"""
    async with MysqlUtil() as conn:
        sql = "select * from dust_fogcan_run_state where pid in %s " \
              "and quarter_time >= %s order by pid, quarter_time"
        dust_list = await conn.fetchall(sql, args=(tuple(pids), now_ymd))
    return dust_list


async def query_dust_water_run_state(water_id, now_ymd):
    """根据pids查询最新雾炮结果"""
    async with MysqlUtil() as conn:
        sql = "select * from dust_water_run_state where water_id in %s " \
              "and quarter_time >= %s order by water_id, quarter_time"
        water_list = await conn.fetchall(sql, args=(tuple(water_id), now_ymd))
    return water_list


async def dust_water_run_state_by_time(start, end, water_id):
    """根据pids查询最新雾炮结果"""
    async with MysqlUtil() as conn:
        sql = "select * from dust_water_run_state where water_id = %s " \
              "and quarter_time>=%s and quarter_time<=%s"
        water_list = await conn.fetchall(sql, args=(water_id, start, end))
    return water_list


async def dust_water_run_day_sum_water(day_str=None):
    """总用水"""
    sql = "SELECT sum(water_total) from dust_water_run_day"
    args = None
    if day_str:
        sql = "SELECT sum(water_total) from dust_water_run_day " \
              "where cal_day = %s"
        args = (day_str,)
    async with MysqlUtil() as conn:
        sum_water = await conn.fetchone(sql, args=args)
    return sum_water


async def dust_fogcan_run_day_sum_kwh(day_str=None):
    """总用电"""
    sql = "SELECT sum(kwh_total) from dust_fogcan_run_day"
    args = None
    if day_str:
        sql = "SELECT sum(kwh_total) from dust_fogcan_run_day " \
              "where cal_day = %s"
        args = (day_str,)
    async with MysqlUtil() as conn:
        sum_kwh = await conn.fetchone(sql, args=args)
    return sum_kwh


async def sum_water_runts_group(start, end):
    """根据water_id分组聚合, 求总用水和运行时间"""
    async with MysqlUtil() as conn:
        sql = "SELECT water_id, SUM(water_total), SUM(run_ts) " \
              "from dust_water_run_day where cal_day >= %s and " \
              "cal_day <= %s group by water_id"
        sum_info = await conn.fetchall(sql, args=(start, end))
    return sum_info


async def sum_kwh_runts_group(start, end):
    """根据pid分组聚合, 求总用电和运行时间"""
    async with MysqlUtil() as conn:
        sql = "SELECT pid, SUM(kwh_total), SUM(run_ts) " \
              "from dust_fogcan_run_day where cal_day >= %s and " \
              "cal_day <= %s group by pid"
        sum_info = await conn.fetchall(sql, args=(start, end))
    return sum_info


async def sum_water_group(start, end, date_type):
    async with MysqlUtil() as conn:
        if date_type == "day":
            sql = "SELECT DATE_FORMAT(quarter_time, '%%Y-%%m-%%d %%H:00:00')" \
                  " AS time, SUM(qr_water_diff) AS water " \
                  "FROM dust_water_run_state " \
                  "WHERE quarter_time BETWEEN %s and %s " \
                  "GROUP BY time ORDER BY time"
        elif date_type == "month":
            sql = """SELECT cal_day, SUM(water_total) as water 
            FROM `dust_water_run_day` where cal_day BETWEEN %s and %s 
            GROUP BY cal_day ORDER BY cal_day"""
        sum_info = await conn.fetchall(sql, args=(start, end))
        return sum_info