from pot_libs.mysql_util.mysql_util import MysqlUtil
from pot_libs.settings import SETTING


async def monitor_point_join_by_points(points):
    """monitor和point关联"""
    sql = "SELECT m.mtid,p.ctnum,m.name, m.m_type, p.pid,p.cid " \
          "FROM monitor m inner join point p on m.mtid = p.mtid " \
          "WHERE p.pid in %s and m.demolished = 0 order by field(p.pid,{})". \
        format(str(points).replace("[", "").replace("]", ""))
    async with MysqlUtil() as conn:
        monitor_point_list = await conn.fetchall(sql, args=(tuple(points),))
    return monitor_point_list


async def monitor_point_join_points(points):
    """monitor和point关联"""
    sql = "SELECT m.mtid, m.name, m.m_type, m.sid, p.pid " \
          "FROM monitor m inner join point p on m.mtid = p.mtid " \
          "WHERE p.pid in %s and m.demolished = 0"
    async with MysqlUtil() as conn:
        monitor_point_list = await conn.fetchall(sql, args=(tuple(points),))
    return monitor_point_list


async def get_electric_datas_dao(table_name, pid, start, end):
    sql = f"SELECT * FROM {table_name} where pid=%s and create_time " \
          f"BETWEEN '{start}' and '{end}' ORDER BY create_time desc"
    async with MysqlUtil() as conn:
        electric_datas = await conn.fetchall(sql, args=(pid,))
    return electric_datas


async def get_qual_history_dao(table_name, pid, start, end, date_format):
    sql = f"SELECT DATE_FORMAT(p.create_time, '{date_format}') date_time, " \
          f"p.* FROM {table_name} p where p.pid=%s and p.create_time " \
          f"BETWEEN '{start}' and '{end}' order by p.create_time"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(pid,))
    return datas


async def get_elec_history_dao(table_name, pid, start, end, date_format):
    sql = f"SELECT DATE_FORMAT(create_time, '{date_format}') date_time, " \
          f"lf_mean, pttl_mean, qttl_mean, costtl_mean, ua_mean, ub_mean, " \
          f"uc_mean, ia_mean, ib_mean, ic_mean, freq_mean, uab_mean, " \
          f"ucb_mean FROM {table_name}  where pid=%s and create_time " \
          f"BETWEEN '{start}' and '{end}' order by create_time"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(pid,))
    return datas


async def get_elec_mtid_sid_by_cid(cid):
    if isinstance(cid, tuple):
        cid_tuple = cid
    elif isinstance(cid, list):
        cid_tuple = tuple(cid)
    else:
        cid_tuple = (cid,)
    sql = (
        f"""
        SELECT
            mtid,
            sid
        FROM
            monitor
        WHERE
            cid in %s AND demolished=0;
        """
    )
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(cid_tuple,))
    return datas if datas else []


async def load_add_to_compy_ids(cid):
    symbol = "in" if isinstance(cid, (list, tuple)) else "="
    db = SETTING.mysql_db
    sql = f"SELECT monitor.mtid, monitor.sid FROM {db}.monitor " \
          f"INNER JOIN {db}.point ON point.mtid=monitor.mtid " \
          f"WHERE monitor.cid {symbol} %s AND point.add_to_company=1 " \
          f"AND monitor.demolished=0;"
    async with MysqlUtil() as conn:
        ids = await conn.fetchall(sql, (cid,))
        return ids if ids else []
