from pot_libs.mysql_util.mysql_util import MysqlUtil


async def tree_monitor_dao(cid, group=1):
    sql = f"""
            select a.id,a.parent_id,a.mtid, a.name ,(case when a.mtid=0 then
            0 else 5 end) as `level`, ifnull(b.m_type,0) m_type
            from monitor_tree a
            left join monitor b on a.mtid = b.mtid
            where a.cid = %s and a.class=%s and (b.demolished = 0 or a.mtid=0)
            order by b.m_type asc,LENGTH(a.name),a.name,a.id asc
    """
    async with MysqlUtil() as conn:
        result = await conn.fetchall(sql, args=(cid, group))
    return result


async def tree_build_dao(cid, group=1):
    sql = f"""
            select a.id,a.parent_id,ifnull(a.mtid,0) mtid,a.name,a.level,
            ifnull(b.m_type,0) m_type,a.class as `group`
            from build_tree a
            left join monitor b on a.mtid = b.mtid
            where a.is_delete = 0 and a.cid = %s and a.class=%s and (
            b.demolished = 0 or a.mtid is null)
            order by a.level desc,LENGTH(a.name),a.name
    """
    async with MysqlUtil() as conn:
        result = await conn.fetchall(sql, args=(cid, group))
    return result


async def monitor_name_list_dao(cid, mtids=None):
    """
    获取监测点列表
    :param cid:
    :param mtids:
    :return:
    """
    where = ""
    if mtids:
        where += f" and mtid in {str(tuple(mtids)).replace(',)', ')')}"
    sql = f"""
            select mtid, name from monitor
            where cid = %s and demolished = 0 {where}
            order by mtid asc
    """
    async with MysqlUtil() as conn:
        result = await conn.fetchall(sql, args=(cid,))
    return result or []


async def tree_name_by_ids(cid, table_name="monitor_tree", ids=None):
    """
    根据id列表获取树名称
    :param cid:
    :param table_name:
    :param ids:
    :return:
    """
    where = ""
    if ids:
        where += f" and a.id in {str(tuple(ids)).replace(',)', ')')}"
    if table_name == "monitor_tree":
        sql = f"""
                select a.id, a.name, a.mtid from monitor_tree a
                where a.cid = %s {where}
                order by a.name, a.id asc
        """
    else:
        sql = f"""
                select a.id, concat(ifnull(b.name,""),"-",a.name) as name,
                a.mtid from build_tree a
                left join  build_tree b on a.parent_id = b.id
                where a.cid = %s {where}
                order by a.name, a.id asc
        """
    async with MysqlUtil() as conn:
        result = await conn.fetchall(sql, args=(cid,))
    return result or []


async def dynamic_env_list_dao(cid, positions=None):
    """
    获取动环列表
    :param cid:
    :param positions:
    :return:
    """
    where = ""
    if positions:
        where += f" and position in {str(tuple(positions)).replace(',)', ')')}"
    sql = f"""
        select position as id,position as name,group_concat(mtid) mtids
        from dynamic_env_device
        where cid = %s {where}
        group by position
        order by position asc
    """
    async with MysqlUtil() as conn:
        result = await conn.fetchall(sql, args=(cid,))
    return result or []
