from pot_libs.mysql_util.mysql_util import MysqlUtil
from pot_libs.logger import log
from unify_api.modules.product_info.procedures.hardware_pds import (
    get_user_hardware_info, hardware_statistics)


async def check_company_exist(company_id):
    '''
        判断工厂是否存在
    '''
    raw_sql = "select count(*) as company_count from company where cid = %s"
    async with MysqlUtil() as conn:
        company_count = await conn.fetchone(sql=raw_sql, args=(company_id))
    return company_count.get('company_count') > 0


async def equip_management_list(company_id, page_num, page_size):
    '''
        获取设备管理的监测点列表,先保留老的写法，后面1.0改版的时候统一改
    '''
    datas = await get_user_hardware_info(company_id, page_num, page_size)
    return_fields = (
        "installed_location", "device_number", "wiring_type", "ct_change",
        "pt_change", "rated_voltage", "start_time")
    return_datas = []
    for data in datas.get('rows'):
        return_one = {}
        for return_field in return_fields:
            return_one[return_field] = data.get(return_field)
        
        return_datas.append(return_one)
    datas['rows'] = return_datas
    return datas


async def equip_management_total(company_id):
    '''
        获取设备管理的汇总信息
    '''
    datas = await hardware_statistics(company_id)
    return datas


async def equip_run_list(company_id, point_ids, start_time, end_time,
                         page_num, page_size, sort_field, sort_type):
    '''
    获取设备运行记录
    '''
    async with MysqlUtil() as conn:
        raw_sql = "SELECT {} from scope_equip_run_record s " \
                  "left join (select pid,max(id) max_id " \
                  "from scope_equip_run_record group by pid) sp " \
                  "on s.pid = sp.pid " \
                  "left join point p on s.pid=p.pid " \
                  "left join monitor_reuse r on p.mtid = r.mtid " \
                  "where " \
                  "(p.cid=%s or r.cid = %s) and s.start_time " \
                  "BETWEEN %s and %s and " \
                  "(s.end_time > 0 or (s.end_time = 0 and s.id = sp.max_id)) "
        if point_ids:
            raw_sql += " and s.pid in %s"
            args = (
                company_id,
                company_id,
                start_time,
                end_time,
                tuple(point_ids)
            )
        else:
            args = (
                company_id,
                company_id,
                start_time,
                end_time,
            )
        # 先总数
        count_sql = raw_sql.format("count(*) as run_count", "")
        count_result = await conn.fetchone(sql=count_sql, args=args)
        
        list_result = []
        if count_result.get("run_count", 0) > 0:
            # 排序字段处理
            if sort_field == 'point_name':
                sort_field = 'p.name'
            elif sort_field == 'run_time':
                sort_field = '(s.end_time-s.start_time)'
            # 再分页列表
            raw_sql = raw_sql.format(
                "s.pid,p.name point_name,s.start_time,s.end_time",
            )
            raw_sql += " order by {} {}  LIMIT %s OFFSET %s".format(sort_field,
                                                                    sort_type)
            if point_ids:
                args = (company_id,
                        company_id,
                        start_time,
                        end_time,
                        tuple(point_ids),
                        page_size,
                        (page_num - 1) * page_size
                        )
            else:
                args = (company_id,
                        company_id,
                        start_time,
                        end_time,
                        page_size,
                        (page_num - 1) * page_size
                        )
            list_result = await conn.fetchall(sql=raw_sql,
                                              args=args)
    return list_result, count_result.get("run_count", 0)


async def equip_run_statistics(company_id, point_ids, start_time, end_time):
    '''
    获取运行统计数据
    '''
    dura_time = "case when end_time > 0 then end_time-start_time else 0 end"
    async with MysqlUtil() as conn:
        count_sql = f"SELECT count(*) as total_count," \
                    f"avg({dura_time}) as avg_time," \
                    f"sum({dura_time}) as all_time," \
                    f"max({dura_time}) as max_time " \
                    "from scope_equip_run_record s " \
                    "left join (select pid,max(id) max_id from " \
                    "scope_equip_run_record group by pid) sp " \
                    "on s.pid = sp.pid " \
                    "left join point p on s.pid=p.pid " \
                    "left join monitor_reuse r on p.mtid = r.mtid " \
                    "where (p.cid=%s or r.cid = %s) " \
                    "and s.start_time BETWEEN %s and %s and (s.end_time > 0 " \
                    "or (s.end_time = 0 and s.id = sp.max_id)) "
        if point_ids:
            count_sql += " and s.pid in %s"
            args = (
                company_id,
                company_id,
                start_time,
                end_time,
                tuple(point_ids)
            )
        else:
            args = (
                company_id,
                company_id,
                start_time,
                end_time,
            )
        count_result = await conn.fetchone(sql=count_sql, args=args)
    return count_result


async def get_equip_run_status(point_id):
    '''
    获取当前设备是否正在运行
    '''
    async with MysqlUtil() as conn:
        # 是否非动力设备
        power_equip_sql = "select is_power_equipment from monitor m " \
                             "left join point p on m.mtid = p.mtid " \
                             "where p.pid = %s"
        power_equip_result = await conn.fetchone(sql=power_equip_sql,
                                                 args=(point_id,))
        if power_equip_result.get("is_power_equipment", 0) == 0:
            return 2
        raw_sql = "select count(*) run_count from scope_equip_run_record s " \
                  "left join (select pid,max(id) max_id from " \
                  "scope_equip_run_record group by pid) sp " \
                  "on s.pid = sp.pid " \
                  "WHERE s.pid= %s and start_time < unix_timestamp(NOW()) " \
                  "and (end_time > unix_timestamp(NOW()) or " \
                  "(end_time=0 and id=max_id)) "
        result = await conn.fetchone(sql=raw_sql, args=(point_id,))
    return 1 if result.get("run_count") > 0 else 0
