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


async def company_by_cids(cids):
    """根据cids查询company信息"""
    sql = "SELECT * from company where cid in %s"
    async with MysqlUtil() as conn:
        company_list = await conn.fetchall(sql, args=(tuple(cids),))
    return company_list


async def point_by_points(point_list):
    sql = "SELECT * from point where pid in %s"
    async with MysqlUtil() as conn:
        point_info_list = await conn.fetchall(sql, args=(tuple(point_list),))
    return point_info_list


async def point_by_pid_mtid(mtid, pid):
    sql = "SELECT * from point where pid = %s and mtid = %s"
    async with MysqlUtil() as conn:
        point_dic = await conn.fetchone(sql, args=(pid, mtid))
    return point_dic


async def storey_by_cid(cid):
    """根据cids查询楼层信息"""
    sql = "SELECT storey_id, storey_name from storey_room_map " \
          "where cid = %s order by storey_id "
    async with MysqlUtil() as conn:
        storey_list = await conn.fetchall(sql, args=(cid,))
    return storey_list


async def query_points_by_storey(storeys):
    """根据storey_id查询point_id和room_name"""
    sql = "SELECT s.storey_id,s.storey_name,s.point_id,s.room_name,m.mtid," \
          "p.ctnum,p.cid from storey_room_map s LEFT JOIN point p " \
          "on p.pid=s.point_id LEFT JOIN monitor m on m.mtid=p.mtid " \
          "where s.storey_id in %s and m.demolished=0 " \
          "order by s.storey_id, s.room_name"
    async with MysqlUtil() as conn:
        points = await conn.fetchall(sql, args=(tuple(storeys),))
    return points


async def points_by_cid(cids):
    """根据cid查询points"""
    sql = "SELECT * FROM point WHERE cid in %s"
    async with MysqlUtil() as conn:
        points = await conn.fetchall(sql, args=(tuple(cids),))
    return points


async def points_monitor_by_cid(cids):
    sql = "SELECT p.pid, m.* FROM `monitor` m LEFT JOIN point p " \
          "on m.mtid=p.mtid where m.cid in %s and m.demolished=0"
    async with MysqlUtil() as conn:
        points = await conn.fetchall(sql, args=(tuple(cids),))
    return points


async def mid_by_pid(pid):
    """根据pid查询mid"""
    sql = "select mtid from point where pid=%s " \
          "order by create_time desc limit 1 "
    async with MysqlUtil() as conn:
        mid_dic = await conn.fetchone(sql, args=(pid,))
    return mid_dic


async def get_point_monitor_dao(pid):
    sql = "SELECT m.meter_no,m.mtid,m.sid," \
          "p.ctr,p.ptr,p.ctnum,p.vc,p.tc,p.imax" \
          "FROM `point` p INNER JOIN monitor m on m.mtid=p.mtid " \
          "where p.pid=%s and m.demolished = 0"
    async with MysqlUtil() as conn:
        datas = await conn.fetchone(sql, args=(pid,))
    return datas


async def get_location_monitor_dao(lid):
    sql = "SELECT l.lid,l.ad_field,m.sid FROM `location` l " \
          "INNER JOIN monitor m on l.mtid=m.mtid where l.lid=%s limit 1"
    async with MysqlUtil() as conn:
        datas = await conn.fetchone(sql, args=(lid,))
    return datas


async def meter_by_mid(mid):
    """根据mid查询meter"""
    sql = "select mid,sid,meter_no from meter where mid=%s"
    async with MysqlUtil() as conn:
        meter_dic = await conn.fetchone(sql, args=(mid,))
    return meter_dic


async def meter_by_mids(mids):
    """根据mids查询meter"""
    sql = "select * from meter where mid in %s"
    async with MysqlUtil() as conn:
        meter_list = await conn.fetchall(sql, args=(tuple(mids),))
    return meter_list


async def item_by_mitd_dao(mtids):
    sql = "select mtid, meter_no from monitor where mtid in %s"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(mtids,))
    return datas


async def monitor_point_storey_join_in(cid, page_num, page_size):
    """monitor和point和storey联合查询, 分页"""
    sql = "SELECT monitor.cid,c.address,point.name,point.create_time, " \
          "monitor.sid, srm.room_name, srm.storey_name, monitor.longitude, " \
          "point.mtid,monitor.latitude,point.pid FROM monitor " \
          "inner join point on monitor.mtid = point.mtid inner join " \
          "storey_room_map srm on point.pid = srm.point_id left join " \
          "company c on c.cid = monitor.cid WHERE monitor.cid in %s " \
          "and monitor.demolished = 0 order by point.pid limit %s, %s"
    async with MysqlUtil() as conn:
        monitor_point_storey_list = await conn.fetchall(sql, args=(
            cid, (page_num - 1) * page_size, page_size))
    return monitor_point_storey_list


async def meter_param_by_mid(mtid):
    """根据mid查询meter, 参数固定不要再增加"""
    sql = "select ctr,ptr,ctnum,vc,tc,imax from point " \
          "where mtid=%s order by create_time desc limit 1"
    async with MysqlUtil() as conn:
        meter_param_dic = await conn.fetchone(sql, args=(mtid,))
    return meter_param_dic


async def change_sensor_by_location(location_id):
    """根据location_id查询change_sensor_record"""
    sql = "select location_id,sid,field from change_sensor_record " \
          "where location_id=%s order by start_time desc limit 1"
    async with MysqlUtil() as conn:
        sensor_dic = await conn.fetchone(sql, args=(location_id,))
    return sensor_dic


async def tsp_by_cid(cid):
    """根据cid查询points"""
    sql = "SELECT tsp_id, name FROM tsp WHERE cid = %s " \
          "order by name"
    async with MysqlUtil() as conn:
        tsp_list = await conn.fetchall(sql, args=(cid,))
    return tsp_list


async def monitor_by_cid(cid):
    """根据cid查询monitor"""
    sql = "SELECT * FROM monitor WHERE cid = %s and demolished = 0"
    async with MysqlUtil() as conn:
        monitor_list = await conn.fetchall(sql, args=(cid,))
    return monitor_list


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


async def monitor_location_join(cid):
    """monitor和location关联"""
    sql = "SELECT m.mtid, l.lid, l.item FROM monitor m " \
          "inner join location l on m.mtid = l.mtid " \
          "WHERE m.cid = %s and m.demolished = 0"
    async with MysqlUtil() as conn:
        monitor_location_list = await conn.fetchall(sql, args=(cid,))
    return monitor_location_list


async def monitor_point_storey_join(cid, page_num, page_size):
    """monitor和point和storey联合查询, 分页"""
    sql = "SELECT srm.mtid,point.pid,point.name,point.create_time, " \
          "monitor.sid, srm.room_name, srm.storey_name FROM monitor " \
          "inner join point on monitor.mtid=point.mtid inner join " \
          "storey_room_map srm " \
          "on point.pid = srm.point_id WHERE monitor.cid = %s " \
          "and monitor.demolished = 0 order by point.pid limit %s, %s"
    async with MysqlUtil() as conn:
        monitor_point_storey_list = await conn.fetchall(sql, args=(
            cid, (page_num - 1) * page_size, page_size))
    return monitor_point_storey_list


async def company_model_by_cid(cid):
    """根据cid查询company_model信息"""
    sql = "SELECT * from company_model where cid = %s"
    async with MysqlUtil() as conn:
        company_model_dic = await conn.fetchone(sql, args=(cid,))
    return company_model_dic


async def inline_zdu_all_by_cid(cid):
    """根据cid查询inline_zdu信息"""
    sql = "SELECT * from inline where cid = %s"
    async with MysqlUtil() as conn:
        inline_zdu_dic = await conn.fetchall(sql, args=(cid,))
    return inline_zdu_dic


async def water_by_cid(cid):
    sql = "SELECT * FROM water WHERE cid = %s"
    async with MysqlUtil() as conn:
        water_list = await conn.fetchall(sql, args=(cid,))
    return water_list


async def storey_wp_by_cid(cid):
    """根据cids查询楼层, 雾炮信息"""
    sql = "SELECT * from storey_room_map where cid = %s and storey_name = '雾炮'"
    async with MysqlUtil() as conn:
        storey_list = await conn.fetchall(sql, args=(cid,))
    return storey_list


async def storey_pl_by_cid(cid):
    """根据cids查询楼层, 喷淋信息"""
    sql = "SELECT * from storey_room_map where cid = %s and storey_name = '喷淋'"
    async with MysqlUtil() as conn:
        storey_list = await conn.fetchall(sql, args=(cid,))
    return storey_list


async def company_extend_by_cid(cid):
    """根据cids查询company信息"""
    sql = "SELECT * from company_extend where cid = %s"
    async with MysqlUtil() as conn:
        company_list = await conn.fetchall(sql, args=(cid,))
    return company_list


async def company_by_cid(cids):
    """根据cids查询company"""
    sql = "SELECT create_time FROM `company` " \
          "where cid in %s ORDER BY create_time;"
    async with MysqlUtil() as conn:
        monitor_list = await conn.fetchall(sql, args=(cids,))
    return monitor_list


async def detection_point_by_cid(cids):
    """根据cids查询检测点总数"""
    sql = "SELECT count(mtid) as point_num FROM `monitor` " \
          "where cid in %s and demolished=0;"
    async with MysqlUtil() as conn:
        detection_point_count = await conn.fetchall(sql, args=(tuple(cids),))
    return detection_point_count


async def monitor_page_by_cid(cids, page_num, page_size):
    """根据cids查询项目名称/总数以及分页"""
    sql = "SELECT a.cid, b.shortname name, count(a.cid) count_num " \
          "FROM monitor a " \
          "left join company b on a.cid=b.cid " \
          "where a.cid in %s and a.demolished = 0 GROUP BY a.cid limit %s, %s;"
    async with MysqlUtil() as conn:
        info_list = await conn.fetchall(sql=sql, args=(
            tuple(cids), (page_num - 1) * page_size, page_size))
    return info_list


async def start_time_by_cids(cids):
    """根据cid查询启用时间"""
    sql = "SELECT cid,create_time FROM `company` where cid in %s and is_show=1"
    async with MysqlUtil() as conn:
        start_times = await conn.fetchall(sql=sql, args=(tuple(cids),))
    return start_times


async def tcs_runtime_by_cids(cids):
    sql = """SELECT cid, sum(tc_runtime) tcs FROM `inline` 
            where cid in %s GROUP BY cid"""
    async with MysqlUtil() as conn:
        tcs_counts = await conn.fetchall(sql=sql, args=(tuple(cids),))
    return tcs_counts


async def user_by_user_id(user_id):
    sql = "SELECT * FROM user where user_id = %s"
    async with MysqlUtil() as conn:
        user_dic = await conn.fetchone(sql=sql, args=(user_id,))
    return user_dic


async def user_by_phone_number(phone):
    sql = "SELECT * FROM user where phone_number = %s and is_delete=0"
    async with MysqlUtil() as conn:
        user_dic = await conn.fetchone(sql=sql, args=(phone,))
    return user_dic


async def meter_by_sid(sid):
    sql = "SELECT mid from meter WHERE sid=%s ORDER BY create_time " \
          "DESC limit 1;"
    async with MysqlUtil() as conn:
        meter_dic = await conn.fetchone(sql=sql, args=(sid,))
    return meter_dic


async def get_all_username():
    sql = "SELECT user_id, real_name as user_name, zhiweiu_auth as role " \
          "FROM `user`"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql=sql)
    username_info = {}
    for data in datas:
        username_info[data["user_id"]] = {
            "user_name": data["user_name"],
            "role": data["role"]
        }
    return username_info


async def monitor_by_mtid(mtid):
    sql = "select * from monitor where mtid = %s "
    async with MysqlUtil() as conn:
        monitor_dic = await conn.fetchone(sql, args=(mtid,))
    return monitor_dic


async def search_iccid(sid):
    sql = "select iccid, sid from sid_iccid where sid = %s "
    async with MysqlUtil() as conn:
        iccid = await conn.fetchone(sql, args=(sid,))
    return iccid


async def save_iccid(sid, iccid):
    sql = "INSERT INTO `sid_iccid` (`sid`, `iccid`) VALUES ( %s, %s) "
    async with MysqlUtil() as conn:
        await conn.execute(sql, args=(sid, iccid))
        log.info(sql % (sid, iccid))


async def get_fields_by_mtid(mtid, table_name="monitor", fields="m_type"):
    """
    通过mtid获取设备表id
    :param mtid:
    :param table_name:
    :param fields:
    :return:
    """
    sql = f"select {fields} from {table_name} where mtid = %s"
    async with MysqlUtil() as conn:
        result = await conn.fetchone(sql, (mtid,))
    return result


async def load_point_pttl_mean(start, end, pid):
    sql = f"SELECT pttl_mean, create_time FROM `point_15min_electric` " \
          f"where pid=%s and create_time BETWEEN '{start}' and '{end}'"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(pid,))
    return datas
